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

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(dot)g(dot)johnston(at)gmail(dot)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.