Re: Bad Plan for Questionnaire-Type Query
| От | David Blewett |
|---|---|
| Тема | Re: Bad Plan for Questionnaire-Type Query |
| Дата | |
| Msg-id | 9d1f8d830905101336j4dfacabet7b5cbd18ccadf374@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Bad Plan for Questionnaire-Type Query (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-performance |
On Sat, May 9, 2009 at 11:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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 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
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 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
В списке pgsql-performance по дате отправления: