On Thu, 1 Jun 2017 16:45:17 +0200 Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx> 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 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? 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@xxxxxxxxxxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
