Re: Plan differences

Поиск
Список
Период
Сортировка
От Adam Pearson
Тема Re: Plan differences
Дата
Msg-id A09FF13F31F4184D8DDD607B8197B98C3C369BBA@dcm1-exm-002.ofg.local
обсуждение исходный текст
Ответ на Re: Plan differences  (Anton Melser <melser.anton@gmail.com>)
Ответы Re: Plan differences  (Anton Melser <melser.anton@gmail.com>)
Список pgsql-performance

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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Anton Melser
Sent: 01 January 2016 5:13 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Plan differences

 

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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Anton Melser
Дата:
Сообщение: Re: Plan differences
Следующее
От: Mark Zealey
Дата:
Сообщение: Proposal for unlogged tables