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

Re: Bad Plan for Questionnaire-Type Query

From: David Blewett <david(at)dawninglight(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bad Plan for Questionnaire-Type Query
Date: 2009-05-22 20:14:45
Message-ID: 9d1f8d830905221314p32ce6b1bi3e2dccba8b4a49e2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, May 9, 2009 at 11:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Blewett <david(at)dawninglight(dot)net> writes:
> > On Fri, May 8, 2009 at 10:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Thanks.  Could I trouble you for one other data point --- about how many
> >> rows are in each of these tables?
>
> > Not a problem:
>
> As best I can tell, the selectivity numbers are about what they should
> be --- for instance, using these stats I get a selectivity of 0.0000074
> for the join clause fkr.submission_id = tr.submission_id.  Over the
> entire relations (646484 and 142698 rows) that's predicting a join size
> of 683551, which seems to be in the right ballpark (it looks like
> actually it's one join row per canvas_foreignkeyresponse row, correct?).


I took the time to load this data into an 8.4beta2 install, and the same
query runs in a much more reasonable timeframe (~3s as opposed to ~50s). I
set the statistics target to 500, and got this explain [1].

David

1. http://explain.depesz.com/s/pw

In response to

Responses

pgsql-performance by date

Next:From: Frank JoerdensDate: 2009-05-23 00:13:08
Subject: Re: Full statement logging problematic on larger machines?
Previous:From: Robert SchnabelDate: 2009-05-22 17:25:55
Subject: Re: raid10 hard disk choice

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