|
Hello Anton, Changing the locale to anything other than C or POSIX will have a performance overhead. I’m pretty sure that just declaring the locale on the
indexes is just like plastering over the cracks. Is it possible to reload the database with the same locale as the original database server? Regards, Adam From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]
On Behalf Of Anton Melser
I spoke a little soon - while many of the simple queries are now hitting the indexes, some of the more complicated ones are still producing substantially inferior plans, even after reloading the whole DB with an identical lc_collate and
lc_ctype. Here are the plans on the original server and the new server (identical collations, lctypes and index types - btree C). I have been experimenting (accepted = accepted2, idx_accepted2_mid = idx_accepted_mid, etc.) and the tables no longer have exactly
the same data but there is nothing substantially different (a few days of data more with about a year total). The oldserver query is actually working on about 3x the amount of data - I tried reducing the amounts on the new server to get done in memory but
it didn't seem to help the plan. HashAggregate (cost=3488512.43..3496556.16 rows=536249 width=143) (actual time=228467.924..229026.799 rows=1426351 loops=1) Group Key: to_char(timezone('UTC'::text, a.tstamp), 'YYYY-MM-DD'::text), a.column1, CASE WHEN (d.column1 IS NOT NULL) THEN d.column1 ELSE fff.column1 END, a.column2 -> Merge Left Join (cost=110018.15..3072358.66 rows=23780215 width=143) (actual time=3281.993..200563.177 rows=23554638 loops=1) Merge Cond: ((a.message_id)::text = (fff.message_id)::text) -> Merge Left Join (cost=110017.58..2781199.04 rows=23780215 width=136) (actual time=3281.942..157385.338 rows=23554636 loops=1) Merge Cond: ((a.message_id)::text = (d.message_id)::text) -> Index Scan using idx_accepted2_mid on accepted a (cost=0.70..2226690.13 rows=23780215 width=83) (actual time=3.690..73048.662 rows=23554632 loops=1) Index Cond: ((message_id)::text > '20151130'::text) Filter: (((mrid)::text <> 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz'::text) AND ((mrid)::text <> 'BAT'::text) AND ((column2)::text <> 'text1'::text) AND ((column2)::text !~~ 'text2.%'::text)) Rows Removed by Filter: 342947 -> Index Scan using idx_delivered2_mid on delivered d (cost=110016.89..482842.01 rows=3459461 width=53) (actual time=3278.245..64031.033 rows=23666434 loops=1) Index Cond: ((message_id)::text > '20151130'::text) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 443 SubPlan 1 -> Index Scan using idx_failed2_mid on failed ff (cost=0.57..109953.48 rows=25083 width=46) (actual time=0.041..3124.642 rows=237026 loops=1) Index Cond: ((message_id)::text > '20151130'::text) Filter: ((severity)::text = 'permanent'::text) Rows Removed by Filter: 5080519 -> Index Scan using idx_failed2_mid on failed fff (cost=0.57..112718.27 rows=25083 width=53) (actual time=0.034..4861.762 rows=236676 loops=1) Index Cond: ((message_id)::text > '20151130'::text) Filter: ((severity)::text = 'permanent'::text) Rows Removed by Filter: 5080519 Planning time: 2.039 ms Execution time: 229076.361 ms HashAggregate (cost=7636055.05..7640148.23 rows=272879 width=143) (actual time=488739.376..488915.545 rows=403741 loops=1) Group Key: to_char(timezone('UTC'::text, a.tstamp), 'YYYY-MM-DD'::text), a.column1, CASE WHEN (d.column1 IS NOT NULL) THEN d.column1 ELSE fff.column1 END, a.column2 -> Hash Right Join (cost=5119277.32..7528101.45 rows=6168777 width=143) (actual time=271256.212..480958.460 rows=6516196 loops=1) Hash Cond: ((d.message_id)::text = (a.message_id)::text) -> Bitmap Heap Scan on delivered2 d (cost=808012.86..3063311.98 rows=3117499 width=53) (actual time=7012.487..194557.307 rows=6604970 loops=1) Recheck Cond: ((message_id)::text > '20151225'::text) Rows Removed by Index Recheck: 113028616 Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 88 Heap Blocks: exact=1146550 lossy=2543948 -> Bitmap Index Scan on idx_delivered_mid (cost=0.00..100075.17 rows=6234997 width=0) (actual time=4414.860..4414.860 rows=6605058 loops=1) Index Cond: ((message_id)::text > '20151225'::text) SubPlan 1 -> Bitmap Heap Scan on failed2 ff (cost=19778.06..707046.73 rows=44634 width=46) (actual time=828.164..1949.687 rows=71500 loops=1) Recheck Cond: ((message_id)::text > '20151225'::text) Filter: ((severity)::text = 'permanent'::text) Rows Removed by Filter: 1257151 Heap Blocks: exact=545606 -> Bitmap Index Scan on idx_failed_mid (cost=0.00..19766.90 rows=1232978 width=0) (actual time=599.864..599.864 rows=1328651 loops=1) Index Cond: ((message_id)::text > '20151225'::text) -> Hash (cost=4173912.75..4173912.75 rows=6168777 width=136) (actual time=264243.046..264243.046 rows=6516194 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 93253kB -> Hash Right Join (cost=3443580.52..4173912.75 rows=6168777 width=136) (actual time=254876.487..261300.772 rows=6516194 loops=1) Hash Cond: ((fff.message_id)::text = (a.message_id)::text) -> Bitmap Heap Scan on failed2 fff (cost=19778.06..707046.73 rows=44634 width=53) (actual time=668.372..3876.360 rows=71500 loops=1) Recheck Cond: ((message_id)::text > '20151225'::text) Filter: ((severity)::text = 'permanent'::text) Rows Removed by Filter: 1257151 Heap Blocks: exact=545606 -> Bitmap Index Scan on idx_failed_mid (cost=0.00..19766.90 rows=1232978 width=0) (actual time=459.303..459.303 rows=1328651 loops=1) Index Cond: ((message_id)::text > '20151225'::text) -> Hash (cost=3304523.24..3304523.24 rows=6168777 width=83) (actual time=254206.923..254206.923 rows=6516194 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 92972kB -> Bitmap Heap Scan on accepted2 a (cost=102690.65..3304523.24 rows=6168777 width=83) (actual time=5493.239..248361.721 rows=6516194 loops=1) Recheck Cond: ((message_id)::text > '20151225'::text) Rows Removed by Index Recheck: 79374688 Filter: (((mrid)::text <> 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz'::text) AND ((mrid)::text <> 'BAT'::text) AND ((column2)::text <> 'text1'::text) AND ((column2)::text !~~ 'text2.%'::text)) Rows Removed by Filter: 163989 Heap Blocks: exact=1434533 lossy=3404597 -> Bitmap Index Scan on idx_accepted_mid (cost=0.00..101148.46 rows=6301568 width=0) (actual time=4806.816..4806.816 rows=6680183 loops=1) Index Cond: ((message_id)::text > '20151225'::text) Planning time: 76.707 ms Execution time: 488939.880 ms Any suggestions on something else to try? Thanks again, Anton |
