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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
