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

Re: PostgreSQL 8.3.3 chooses wrong query plan when LIMIT 1 added?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL 8.3.3 chooses wrong query plan when LIMIT 1 added?
Date: 2008-10-27 19:51:57
Message-ID: 23463.1225137117@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)uk> writes:
> I'm experiencing a strange issue with PostgreSQL 8.3.3 whereby adding 
> "LIMIT 1" to the query increases the query time from several 10s of ms 
> to over 5s, and was wondering if anyone with more planner-fu can shed 
> some light on this.

It's hoping that the backwards scan will hit a row with the requested
file_id quickly; which might be true on average but isn't true for this
particular file_id (nor, presumably, any file_id that hasn't been
updated recently).

You might consider a two-column index on (file_id, revision_id) to
make this type of query fast.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-10-27 20:01:49
Subject: Re: empty table explain...
Previous:From: Angel AlvarezDate: 2008-10-27 19:45:20
Subject: Re: nearest neighbor search from xyz coordinates

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