Declaring new indexes with COLLATE "C" and removing the old indexes fixed the like problem but it created a another - the > and < queries need a sort before passing off the the new index. Having two indexes seems to give me the best of both worlds, though obviously it's taking up (much) more space. As space isn't ever likely to be a problem, and there are no updates (only copy) to these tables, I'll keep it like this to avoid having to reload the entire DB.
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
