Re: Order by (for 15 rows) adds 30 seconds to query time

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Richard Neill" <rn214(at)cam(dot)ac(dot)uk>, "PostgreSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Order by (for 15 rows) adds 30 seconds to query time
Date: 2009-12-01 20:06:06
Message-ID: 4B1522CE020000250002CEB1@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Richard Neill <rn214(at)cam(dot)ac(dot)uk> wrote:

> I'd expect the ORDER BY to be the last thing that runs

> Nested Loop Left Join (cost=0.00..727737158.77
> rows=806903677108 width=195) (actual time=31739.052..32862.322
> rows=15 loops=1)

It probably would if it knew there were going to be 15 rows to sort.
It is estimating that there will be 806,903,677,108 rows, in which
case it thinks that using the index will be faster. The question is
why it's 10 or 11 orders of magnitude off on the estimate of result
rows. Could you show us the table definitions underlying that view?

-Kevin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Neill 2009-12-01 22:46:29 Re: Order by (for 15 rows) adds 30 seconds to query time
Previous Message Jean-Michel Pouré 2009-12-01 20:06:05 Re: Order by (for 15 rows) adds 30 seconds to query time