Dmitry Teslenko <dteslenko(at)gmail(dot)com> wrote:
> Seqscan is very slow and I want to avoid it. Subquery is very fast
> and i don't know why postgres chooses that plan.
> I know I can set enable_seqscan = off.
> Is there other ways to enforce index usage?
If you come at it from that angle, you probably won't get the best
resolution. PostgreSQL can see the alternative plans, and develops
estimated costs of running each. It uses the one that it thinks
will be fastest. If it's wrong, there's probably something wrong
with the statistics it uses for estimating, or with the costing
information. (There are some cases where it's not able to
accurately estimate costs even if these are right, but let's check
the more common cases first.)
Please provide a little more information, like PostgreSQL version,
the postgresql.conf contents (excluding comments), OS, hardware, and
the EXPLAIN ANALYZE output of the query with and without
enable_seqscan = off.
Other useful ideas here:
In response to
pgsql-performance by date
|Next:||From: Scott Marlowe||Date: 2010-09-23 17:11:50|
|Subject: Re: Useless sort by|
|Previous:||From: Dmitry Teslenko||Date: 2010-09-23 14:26:17|
|Subject: how to enforce index sub-select over filter+seqscan|