Skip to content

Instantly share code, notes, and snippets.

/query_plans.txt Secret

Created April 27, 2016 12:52
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 anonymous/109f4377f612862b3579b8b5cf1253f4 to your computer and use it in GitHub Desktop.
Save anonymous/109f4377f612862b3579b8b5cf1253f4 to your computer and use it in GitHub Desktop.
Download ZIP
Query plans for 2 union selects (PG 9.4.7)
Raw
query_plans.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.84..285.10 rows=8 width=181) (actual time=0.029..0.792 rows=114 loops=1)
-> Nested Loop (cost=0.84..95.00 rows=2 width=149) (actual time=0.028..0.340 rows=4 loops=1)
-> Index Scan using idx_case_clients_client_id on case_clients cacl (cost=0.42..27.38 rows=8 width=4) (actual time=0.009..0.064 rows=114 loops=1)
Index Cond: (client_id = 12046)
-> Index Scan using cases_pkey on cases (cost=0.42..8.44 rows=1 width=149) (actual time=0.002..0.002 rows=0 loops=114)
Index Cond: (id = cacl.case_id)
Filter: (create_stamp < '1996-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..95.02 rows=3 width=149) (actual time=0.007..0.220 rows=10 loops=1)
-> Index Scan using idx_case_clients_client_id on case_clients cacl_1 (cost=0.42..27.38 rows=8 width=4) (actual time=0.004..0.024 rows=114 loops=1)
Index Cond: (client_id = 12046)
-> Index Scan using cases_pkey on cases cases_1 (cost=0.42..8.45 rows=1 width=149) (actual time=0.002..0.002 rows=0 loops=114)
Index Cond: (id = cacl_1.case_id)
Filter: ((create_stamp >= '1996-01-01 00:00:00'::timestamp without time zone) AND (create_stamp <= '2006-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..95.00 rows=3 width=149) (actual time=0.032..0.223 rows=100 loops=1)
-> Index Scan using idx_case_clients_client_id on case_clients cacl_2 (cost=0.42..27.38 rows=8 width=4) (actual time=0.003..0.028 rows=114 loops=1)
Index Cond: (client_id = 12046)
-> Index Scan using cases_pkey on cases cases_2 (cost=0.42..8.44 rows=1 width=149) (actual time=0.001..0.001 rows=1 loops=114)
Index Cond: (id = cacl_2.case_id)
Filter: (create_stamp > '2006-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 0
Planning time: 0.910 ms
Execution time: 0.877 ms
(24 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=27.48..18164.06 rows=14 width=181) (actual time=28.162..128.207 rows=114 loops=1)
Hash Cond: (cases.id = cacl.case_id)
-> Append (cost=0.00..16448.35 rows=135047 width=181) (actual time=0.006..118.496 rows=135047 loops=1)
-> Seq Scan on cases (cost=0.00..4920.09 rows=41615 width=149) (actual time=0.006..37.094 rows=41976 loops=1)
Filter: (create_stamp < '1996-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 93071
-> Seq Scan on cases cases_1 (cost=0.00..5257.70 rows=44938 width=149) (actual time=0.021..38.215 rows=45034 loops=1)
Filter: ((create_stamp >= '1996-01-01 00:00:00'::timestamp without time zone) AND (create_stamp <= '2006-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 90013
-> Seq Scan on cases cases_2 (cost=0.00..4920.09 rows=48494 width=149) (actual time=0.620..36.226 rows=48037 loops=1)
Filter: (create_stamp > '2006-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 87010
-> Hash (cost=27.38..27.38 rows=8 width=4) (actual time=0.055..0.055 rows=114 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Index Scan using idx_case_clients_client_id on case_clients cacl (cost=0.42..27.38 rows=8 width=4) (actual time=0.006..0.043 rows=114 loops=1)
Index Cond: (client_id = 12046)
Planning time: 0.454 ms
Execution time: 128.255 ms
(18 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment