On Thu, 1 Jun 2017 16:45:17 +0200
Andreas Kretschmer <andreas@a-kretschmer.de> wrote:
>
> 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
whereasa longer 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?
LIKE queries are probably challenging to plan, especially when they're not
left-anchored: how can the planner be reasonalbly expected to estimate how
many rows will be matched by a given LIKE expression.
Not having looked at the code, I would guess that the length of the LIKE
expression will make the planner assume that the match is more restrictive,
while many % and _ in the LIKE expression make the planner assume that the
match is less restrictive. Extrapolate that into guessing a number of matched
tuples and how that fits into the overall plan and you'll probaby give
yourself a brain anuerism. While having a detailed understanding of exactly
how the planner makes such decisions is certainly worthwhile, I would
recommend a more pragmatic approach: try things and see what works.
That in mind, let me throw pg_trgm into the mix of things to try:
https://www.postgresql.org/docs/current/static/pgtrgm.html
The trigram module allows you to create indexes that LIKE can use
to do index searches instead of always having to do sequential scans
or push the LIKE matching to another part of the plan tree. Based on
your described situation, I have a theory that it might improve things
quite a bit.
--
Bill Moran <wmoran@potentialtech.com>