pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John A Meinel <john@arbash-meinel.com>
To: Michael McFarland <sidlonDoesntLikeSpam@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: adding 'limit' leads to very slow query
Date: Mon, 07 Mar 2005 22:46:37 -0600
Message-ID: <422D2E2D.5080305@arbash-meinel.com> (raw)
In-Reply-To: <opsnaj0hctsvs6tg@localhost.localdomain>
References: <opsnaj0hctsvs6tg@localhost.localdomain>

Michael McFarland wrote:

>   I'm trying to understand why a particular query is slow, and it 
> seems  like the optimizer is choosing a strange plan.  See this summary:
>
...

>> explain select * from foo where barId = 412 order by id desc limit 25;
>
> Limit ()
>  ->   Index scan backward using primarykey_index
>   Filter:  barID = 412
>
>
> Could anyone shed some light on what might be happening here?
>
>  - Michael

It is using the wrong index. The problem is that order by + limit 
generally means that you can use the index on the "+" to get the items 
in the correct order. In this case, however, you need it to find all of 
the barId=412 first, since apparently that is more selective than the limit.

It really sounds like the postgres statistics are out of date. And 
either you haven't run vacuum analyze recently, or you need to keep 
higher statistics on either one or both of barId and id.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (5+ messages)  latest in thread

Message-ID: <422D2E2D.5080305@arbash-meinel.com>
Permalink:  ../422D2E2D.5080305@arbash-meinel.com/
Also on:    postgresql.org/message-id/422D2E2D.5080305@arbash-meinel.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: john@arbash-meinel.com, sidlonDoesntLikeSpam@yahoo.com
  Subject: Re: adding 'limit' leads to very slow query
  In-Reply-To: <422D2E2D.5080305@arbash-meinel.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox