Re: Bad Plan for Questionnaire-Type Query

On Sat, May 9, 2009 at 11:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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?).

The design is that for each submission_id there are any number of responses
of different types. This particular questionnaire has 78 questions, 2 of
which are text responses and 28 are foreignkey responses. The restrictions
on the question_id limit the rows returned from those tables to 1 each in
this case however. So yes, it's one to one in this case.

> How is it that each fkr row matching those question_ids has a join match
> in tr that has those other two question_ids? It seems like there must
> be a whole lot of hidden correlation here.

As I mentioned before, they are all linked by the submission_id which
indicates they are part of a single submission against a particular
questionnaire (chart_id in the ddl). It is a design that I based on Elein
Mustain's * <http://www.varlena.com/>*Question/Answer problem [1]. This
particular query includes 2 chart_id's because they contain virtually the
same data (sets of questions), but have different validation requirements.
Does that shed any more light?

Thanks again for the help.

David

1. http://www.varlena.com/GeneralBits/110.php