Re: Slow query plan used

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 longer like expression or use of single %-sign creates a much slower query. Please see below query plans. Most surprisingly to me is the influence of the like expression, especially the doubled %-sign on short expressions. Any other ideas 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=38545 loops=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.011 rows=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.683 rows=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) (actual time=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.183 rows=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