Re: [GENERAL] Slow query plan used - Mailing list pgsql-general

Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen):
>>> Only 130 rows out of the 30000 have ARCHIVED = 0
>> in this case i would suggest a partial index:
>> create index <indexname> on <tablename> (archived) where archived = 0;
> Thanks, Andreas.
>
> Sorry for the confusion about the table names.
> The hint with the partial index sounds as it could solve the problem. I will test it.
>

Hi,

I created now a partial index
    create index on document (archived) where archived = '0';

But result is same as before: a short like expression included in doubled %-signs leads to a fast query plan whereas a
longerlike expression or use of single %-sign creates a much slower query. Please see below query plans. Most
surprisinglyto me is the influence of the like expression, especially the doubled %-sign on short expressions. Any
otherideas how to speed up that query or what is going on here in general? 

Thanks,
Jürgen

Limit  (cost=24327.12..24327.21 rows=38 width=662) (actual time=15373.542..15373.565 rows=136 loops=1)
  Buffers: shared hit=264747 read=51242 dirtied=6 written=16
  ->  Sort  (cost=24327.12..24327.21 rows=38 width=662) (actual time=15373.541..15373.557 rows=136 loops=1)
        Sort Key: document.created, document.id
        Sort Method: quicksort  Memory: 102kB
        Buffers: shared hit=264747 read=51242 dirtied=6 written=16
        ->  Nested Loop  (cost=0.42..24326.12 rows=38 width=662) (actual time=10.951..15372.914 rows=136 loops=1)
              Buffers: shared hit=264741 read=51242 dirtied=6 written=16
              ->  Seq Scan on document_index  (cost=0.00..15403.68 rows=1257 width=289) (actual time=0.205..14901.743
rows=38545loops=1) 
                    Filter: ((lower((searchfield1)::text) ~~ '%sehr%'::text) OR (lower(searchfield8) ~~
'%sehr%'::text))
                    Rows Removed by Filter: 40531
                    Buffers: shared hit=123181 read=38272 dirtied=5 written=12
              ->  Index Scan using document_6720023941 on document  (cost=0.42..7.09 rows=1 width=381) (actual
time=0.011..0.011rows=0 loops=38545) 
                    Index Cond: (id = document_index.documentid)
                    Filter: (((folder)::text = ANY ('{*INBOX,*DELAYED}'::text[])) AND (archived = '0'::bpchar) AND
((umr_actualtopicid)::text= ANY
('{f3fb345741000000,8048405641000000,4fc81b5541000000,d27d9c4d41000200,e9aba54d41000000,4aaf905441000a00,737(...) 
                    Rows Removed by Filter: 1
                    Buffers: shared hit=141560 read=12970 dirtied=1 written=4
Total runtime: 15373.763 ms


Limit  (cost=34194.37..34194.74 rows=150 width=662) (actual time=181.502..181.525 rows=134 loops=1)
  Buffers: shared hit=8022 read=277
  ->  Sort  (cost=34194.37..34194.83 rows=186 width=662) (actual time=181.501..181.516 rows=134 loops=1)
        Sort Key: document.created, c3k_document.id
        Sort Method: quicksort  Memory: 101kB
        Buffers: shared hit=8022 read=277
        ->  Nested Loop  (cost=3546.02..34187.36 rows=186 width=662) (actual time=32.660..181.064 rows=134 loops=1)
              Buffers: shared hit=8022 read=277
              ->  Bitmap Heap Scan on document  (cost=3545.61..19272.79 rows=2375 width=381) (actual
time=22.771..96.683rows=458 loops=1) 
                    Recheck Cond: ((archived = '0'::bpchar) AND (ownerid = ANY
('{5000239,5000238,5000234,5000113,5000237,5000236,5000230,5000112,5000233,5000111,5000232,13,15,16,18,19,5000249,5000246,5000124,5000245,5000127,5000247,5000242,5000120,5000123
(...)
                    Rows Removed by Index Recheck: 15733
                    Filter: ((actualtopicid)::text = ANY
('{f3fb345741000000,8048405641000000,4fc81b5541000000,d27d9c4d41000200,e9aba54d41000000,4aaf905441000a00,737e9c4d41000900,3ecdec4d41000000,4aaf905441000800,4aaf905441000e00,fc7e9c4d41000f00,11ffc
(...)
                    Rows Removed by Filter: 27
                    Buffers: shared hit=5677
                    ->  BitmapAnd  (cost=3545.61..3545.61 rows=6228 width=0) (actual time=22.056..22.056 rows=0
loops=1)
                          Buffers: shared hit=2470
                          ->  Bitmap Index Scan on document_archived_idx  (cost=0.00..1131.17 rows=54784 width=0)
(actualtime=11.694..11.694 rows=60295 loops=1) 
                                Index Cond: (archived = '0'::bpchar)
                                Buffers: shared hit=184
                          ->  Bitmap Index Scan on document_ownerid_folder_status_idx  (cost=0.00..2413.00 rows=8973
width=0)(actual time=8.718..8.718 rows=14962 loops=1) 
                                Index Cond: ((ownerid = ANY
('{5000239,5000238,5000234,5000113,5000237,5000236,5000230,5000112,5000233,5000111,5000232,13,15,16,18,19,5000249,5000246,5000124,5000245,5000127,5000247,5000242,5000120,5000123,5000244,5000122,50
(...)
                                Buffers: shared hit=2286
              ->  Index Scan using document_7965268402 on document_index  (cost=0.42..6.27 rows=1 width=289) (actual
time=0.182..0.183rows=0 loops=458) 
                    Index Cond: (documentid = document.id)
                    Filter: ((lower((searchfield1)::text) ~~ '%%sehr%%'::text) OR (lower(searchfield8) ~~
'%%sehr%%'::text))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=2345 read=277
Total runtime: 184.053 ms