Thanks for reply, David. I have searched internet and changed one parameter cpu_tuple_cost from 0.01 to 0.08. I would see if it helped. I found the problem occurred randomly.
For tsv, I thought if there is an index already built, postgresql should try to make use of it because I think for most of the time it would be faster than full table scan. Thanks and regards, Patrick On Wednesday, September 3, 2014 3:00 AM, David G Johnston <david.g.johns...@gmail.com> wrote: Patrick Dung-2 wrote > Hello Postgresql users, > > In my setting, I found that sometimes the query does not use the gin index > built for a tsv column. > > Attached file provide more info (with explain analyze). So the difference between the first and third queries shown is the fact that the data is now in-memory when the third query is run; in both plans only sequential scans are used. The difference between the first and second queries, where the index is used, is therefore at least partially - if not wholly - due to those same caching effects. On a reasonably small table it is not that unreasonable for the planner to choose a single retrieve-and-filter sequential scan as opposed to a piecemeal lookup-and-retrieve index scan. Your example is not controlled enough to prove that the planner has made an incorrect decision. The estimated costs of 34.5k vs 35.3k are extremely small and since the 34.5k is the result of changing random_page_cost you cannot directly compare them anyway. You need to use "enable_seqscan" and related configuration parameters so that you can force the planner to choose different plans without changing the underlying costs. http://www.postgresql.org/docs/9.3/static/runtime-config-query.html David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-about-gin-index-not-used-on-a-tsv-column-tp5817433p5817438.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general