Skip site navigation (1) Skip section navigation (2)

Re: how to enforce index sub-select over filter+seqscan

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Dmitry Teslenko" <dteslenko(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: how to enforce index sub-select over filter+seqscan
Date: 2010-09-23 15:43:35
Message-ID: 4C9B2F570200002500035C00@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
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:
 
http://wiki.postgresql.org/wiki/SlowQueryQuestions
 
-Kevin

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-09-23 17:11:50
Subject: Re: Useless sort by
Previous:From: Dmitry TeslenkoDate: 2010-09-23 14:26:17
Subject: how to enforce index sub-select over filter+seqscan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group