Re: Question about gin index not used on a tsv column

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.