Postgres Professional
   

Re: Bad Plan for Questionnaire-Type Query

From: Tom Lane
Subject: Re: Bad Plan for Questionnaire-Type Query
Date: ,
Msg-id: 27080.1243190522@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Bad Plan for Questionnaire-Type Query  (David Blewett)
Responses: Re: Bad Plan for Questionnaire-Type Query  (David Blewett)
List: pgsql-performance

Tree view

Bad Plan for Questionnaire-Type Query  (David Blewett, )
 Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
  Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
   Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
    Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
     Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
      Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
 Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
  Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
   Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
    Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
    Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
     Re: Bad Plan for Questionnaire-Type Query  (Tom Lane, )
      Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
       Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
        Re: Bad Plan for Questionnaire-Type Query  (Josh Berkus, )
         Re: Bad Plan for Questionnaire-Type Query  (David Blewett, )
          Re: Bad Plan for Questionnaire-Type Query  (Robert Haas, ) 
David Blewett <> writes:
> 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].
> 1. http://explain.depesz.com/s/pw

Hmm... the join size estimates are no better than before, so I'm afraid
that 8.4 is just as vulnerable to picking a bad plan as the previous
versions were.  I don't think you should assume anything's been fixed.

It still feels like this schema design is obscuring correlations that
the planner needs to know about in order to make decent estimates.
You mentioned earlier that the seemingly unrelated question_ids were
linked via a common submission_id.  I wonder whether it's possible to
query using the submission_id instead?

            regards, tom lane

pgsql-performance by date:

Previous:
From: Łukasz Jagiełło
Date:
Subject: Problems with autovacuum
Next:
From: Shaul Dar
Date:
Subject: Putting tables or indexes in SSD or RAM: avoiding double caching?