I do still have the 9.6.2 instance.
Here is the explain analyze results from the 9.6.2 instance:
Aggregate (cost=764612.56..764612.57 rows=1 width=8) (actual
time=695.166..695.166 rows=1 loops=1)
CTE test
-> Nested Loop (cost=3345.90..757594.63 rows=311908 width=4) (actual
time=76.936..632.852 rows=73500 loops=1)
-> Hash Left Join (cost=3345.47..406121.74 rows=83289 width=16)
(actual time=76.920..264.217 rows=73500 loops=1)
Hash Cond: ((g.text_field)::text = (i.text_field)::text)
-> Gather (cost=3333.89..404626.67 rows=333156 width=30)
(actual time=76.877..219.843 rows=73500 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Nested Loop (cost=2333.89..370311.07 rows=83289
width=30) (actual time=74.266..408.763 rows=14700 loops=5)
-> Hash Join (cost=2333.33..215520.29
rows=24240 width=16) (actual time=74.226..295.244 rows=14652 loops=5)
Hash Cond: (e.id1 = a.id1)
Join Filter: ((a.created_at >=
e.effective_at) AND (a.created_at < e.expired_at))
Rows Removed by Join Filter: 66412
-> Parallel Seq Scan on dim_1 e
(cost=0.00..133595.06 rows=153406 width=20) (actual time=0.029..137.850
rows=141713 loops=5)
-> Hash (cost=1417.59..1417.59
rows=73259 width=20) (actual time=73.284..73.284 rows=73259 loops=5)
Buckets: 131072 Batches: 1 Memory
Usage: 5031kB
-> Seq Scan on staging a
(cost=0.00..1417.59 rows=73259 width=20) (actual time=0.016..36.003
rows=73259 loops=5)
-> Index Scan using dim_2_id on dim_2 g
(cost=0.56..6.36 rows=3 width=38) (actual time=0.006..0.007 rows=1
loops=73259)
Index Cond: ((id2 = a.id2) AND
(a.created_at >= effective_at))
Filter: (a.created_at < expired_at)
Rows Removed by Filter: 1
-> Hash (cost=10.70..10.70 rows=70 width=516) (actual
time=0.028..0.028 rows=27 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on dim_3 i (cost=0.00..10.70 rows=70
width=516) (actual time=0.005..0.015 rows=27 loops=1)
-> Index Scan using dim_4_id on dim_4 h (cost=0.43..4.18 rows=4
width=20) (actual time=0.004..0.004 rows=1 loops=73500)
Index Cond: ((id4 = a.id4) AND (a.created_at >=
effective_at))
Filter: (a.created_at < expired_at)
Rows Removed by Filter: 0
-> CTE Scan on test (cost=0.00..6238.16 rows=311908 width=0) (actual
time=76.938..675.796 rows=73500 loops=1)
Planning time: 0.618 ms
Execution time: 701.594 ms
And here is the result from 9.6.3:
Aggregate (cost=836957.12..836957.13 rows=1 width=8) (actual
time=803.091..803.091 rows=1 loops=1)
CTE test
-> Gather (cost=3335.93..815257.26 rows=964438 width=4) (actual
time=80.394..713.993 rows=73259 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Nested Loop (cost=2335.93..717813.46 rows=241110 width=4)
(actual time=99.061..755.383 rows=14652 loops=5)
-> Hash Left Join (cost=2335.50..333199.71 rows=68143
width=16) (actual time=99.025..579.332 rows=14652 loops=5)
Hash Cond: ((g.text_field)::text =
(i.text_field)::text)
-> Nested Loop (cost=2333.89..332924.06 rows=68143
width=30) (actual time=98.905..562.756 rows=14652 loops=5)
-> Hash Join (cost=2333.33..196015.85
rows=21048 width=16) (actual time=98.858..369.890 rows=14652 loops=5)
Hash Cond: (e.id1 = a.id1)
Join Filter: ((a.created_at >=
e.effective_at) AND (a.created_at < e.expired_at))
Rows Removed by Join Filter: 66412
-> Parallel Seq Scan on dim_1 e
(cost=0.00..91462.65 rows=177066 width=20) (actual time=0.032..165.829
rows=141713 loops=5)
-> Hash (cost=1417.59..1417.59
rows=73259 width=20) (actual time=97.828..97.828 rows=73259 loops=5)
Buckets: 131072 Batches: 1 Memory
Usage: 5031kB
-> Seq Scan on staging a
(cost=0.00..1417.59 rows=73259 width=20) (actual time=0.019..44.612
rows=73259 loops=5)
-> Index Scan using dim_2_id on dim_2 g
(cost=0.56..6.47 rows=3 width=38) (actual time=0.011..0.011 rows=1
loops=73259)
Index Cond: ((id2 = a.id2) AND
(a.created_at >= effective_at))
Filter: (a.created_at < expired_at)
Rows Removed by Filter: 1
-> Hash (cost=1.27..1.27 rows=27 width=516) (actual
time=0.053..0.053 rows=27 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on dim_3 i (cost=0.00..1.27
rows=27 width=516) (actual time=0.023..0.034 rows=27 loops=5)
-> Index Scan using dim_4_id on dim_4 h (cost=0.43..5.60
rows=4 width=20) (actual time=0.010..0.010 rows=1 loops=73259)
Index Cond: ((id4 = a.id4) AND (a.created_at >=
effective_at))
Filter: (a.created_at < expired_at)
Rows Removed by Filter: 0
-> CTE Scan on test (cost=0.00..19288.76 rows=964438 width=0) (actual
time=80.397..770.944 rows=73259 loops=1)
Planning time: 0.756 ms
Execution time: 815.729 ms
Thanks again for any help.
Greig
--
View this message in context: http://www.postgresql-archive.org/Bug-in-postgres-9-6-2-tp5972185p5972217.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
