On Sat, May 9, 2009 at 11:52 AM, Tom Lane <tgl@xxxxxxxxxxxxx> 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
