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

From Andreas Kretschmer
Subject Re: [GENERAL] Slow query plan used
Date
Msg-id 7a1ecbc7-d02a-3792-8381-b288f9d4b67e@a-kretschmer.de
Whole thread Raw
In response to Re: [GENERAL] Slow query plan used  ("Wetzel, Juergen (Juergen)" <wetzel@avaya.com>)
Responses Re: [GENERAL] Slow query plan used  (Bill Moran <wmoran@potentialtech.com>)
Re: [GENERAL] Slow query plan used  ("Wetzel, Juergen (Juergen)" <wetzel@avaya.com>)
List pgsql-general
Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen):
>
> 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';

just to be sure: this syntay is wrong, missing index-name. But it seems
the index is document_archived_idx ...


>
> But result is same as before: a short like expression included in doubled %-signs leads to a fast query plan whereas
alonger like 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? 
>

please consider my plan B) and increase the stats. See my other mail.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Redo the filenode link in tablespace
Next
From: Bill Moran
Date:
Subject: Re: [GENERAL] Slow query plan used