Skip to content

Instantly share code, notes, and snippets.

@jehie

jehie/max_groupby_query Secret

Created May 4, 2017 10:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jehie/ca9fac16b6e3c19612d815446a0e1bc0 to your computer and use it in GitHub Desktop.
Save jehie/ca9fac16b6e3c19612d815446a0e1bc0 to your computer and use it in GitHub Desktop.
Download ZIP
max_groupby_query
Raw
max_groupby_query
SQL and Query Plan for the max() and group_by query.
SQL:
EXPLAIN ANALYZE
SELECT max(released), author_id
FROM book
WHERE author_id in ('1', '2', '3', '4', '5')
AND released<=to_timestamp(2e9)
AND released>=to_timestamp(0)
GROUP BY author_id
Query plan:
"HashAggregate (cost=63902.29..63902.79 rows=50 width=10) (actual time=269.576..269.577 rows=5 loops=1)"
" Group Key: author_id"
" -> Bitmap Heap Scan on book (cost=19036.89..61422.78 rows=495901 width=10) (actual time=163.825..192.015 rows=500000 loops=1)"
" Recheck Cond: (((author_id)::text = ANY ('{1,2,3,4,5}'::text[])) AND (released <= '2033-05-18 06:33:20+03'::timestamp with time zone) AND (released >= '1970-01-01 02:00:00+02'::timestamp with time zone))"
" Heap Blocks: exact=3185"
" -> Bitmap Index Scan on book_idx1 (cost=0.00..18912.91 rows=495901 width=0) (actual time=163.441..163.441 rows=500000 loops=1)"
" Index Cond: (((author_id)::text = ANY ('{1,2,3,4,5}'::text[])) AND (released <= '2033-05-18 06:33:20+03'::timestamp with time zone) AND (released >= '1970-01-01 02:00:00+02'::timestamp with time zone))"
"Planning time: 0.501 ms"
"Execution time: 269.699 ms"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment