Re: [BUGS] Re: 9.2.4: Strange behavior when wildcard is on left side of search string
David Johnston wrote: > varchar(100) > The other option to index words via the full-text search capabilities. Or for columns this short, a similarity search on a trigram index. http://www.postgresql.org/docs/current/interactive/pgtrgm.html -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: 9.2.4: Strange behavior when wildcard is on left side of search string
On 7 April 2013 15:52, Kevin Grittner wrote: > David Johnston wrote: > > > varchar(100) > > > The other option to index words via the full-text search capabilities. > > Or for columns this short, a similarity search on a trigram index. > > http://www.postgresql.org/docs/current/interactive/pgtrgm.html > > Or for columns this short, a similarity search on a trigram index. I had actually thought of that as the next thing to try out [?] My understanding from your replies is that this behavior with *b-tree indices* is not considered a bug but rather a case of "works as designed", yet still and apart from the solution of my particular problem, the fact that in 9.2.4 it is so bad that a simple query sometimes does not finish after 10+ minutes with incessant disk activity tends to look like a problem to me. Thank you very much, gentlemen for your consideration. RD <<332.gif>>
Re: [BUGS] Re: 9.2.4: Strange behavior when wildcard is on left side of search string
ERR ORR wrote: > the fact that in 9.2.4 it is so bad that a simple query sometimes > does not finish after 10+ minutes with incessant disk activity > tends to look like a problem to me. Try running a VACUUM FREEZE ANALYZE; command on the database, under a database superuser ID. If you upgraded with pg_dump (or some other logical population of the data, versus a pg_upgrade run) you are probably getting bitten by the initial setting of hint bits. http://wiki.postgresql.org/wiki/Hint_Bits See if it is still slow after that -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.2.4: Strange behavior when wildcard is on left side of search string
This doesn't seem either buggy or strange... An index on the ordering of that column is not helpful in handling a leading wildcard, and so the query optimizer will, in such cases, revert, correctly, to using a sequential scan and filtering the results. If you have cases where this sort of wildcard needs to be fast, a functional index could help. Create index foo on tbl (reverse(col)); And reverse the wildcard so the index is usable: Select * from tbl where reverse(col) like 'esrever%'; That query can harness the reversed index. Unfortunately, no ordered index helps with Select * from too where col like '%something%'; For that, a full text search index can help, but that is a longer story. At any rate, what you are observing is no surprise, and consistent with what many database systems do.
Re: [BUGS] Re: 9.2.4: Strange behavior when wildcard is on left side of search string
ERR ORR writes: > My understanding from your replies is that this behavior with *b-tree > indices* is not considered a bug but rather a case of "works as designed", > yet still and apart from the solution of my particular problem, the fact > that in 9.2.4 it is so bad that a simple query sometimes does not finish > after 10+ minutes with incessant disk activity tends to look like a problem > to me. It is no better or worse in 9.2.4 than in any prior release. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
