On Sat, May 9, 2009 at 11:52 AM, Tom Lane <t...@sss.pgh.pa.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
