From: | Steve Tucknott <steve(at)retsol(dot)co(dot)uk> |
---|---|
To: | PostGreSQL <pgsql-novice(at)postgresql(dot)org> |
Subject: | Sequential Scans On Complex Query With UNION - see why this fails |
Date: | 2006-01-02 09:10:45 |
Message-ID: | 1136193045.4179.1.camel@retsol1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
PostgreSql 8.0.3
I have a complex query that I want to read the data in a hierarchical
manner - ie from master table filtered rows to sub set1, from subset1 to
subset2 etc. The query is in the attached file, as is the explain. What
I do not understand is why I get sequential scans on certain tables - ie
the purchaseOrdDet, product, supplierProduct when each of the joins
linking the tables hits a valid 'record number' based index.
The query is trying to find all purchase orders within a period, then
all the lines for those orders, products for those lines etc. The union
selects from the supplierproduct/product tables on both sides - in one
case it uses the index and on the other does a sequential scan.
I am obviously doing something wrong in the structure of the query - any
ideas?
Also,...as a quick 'method' question..when writing embedded joins, which
syntax is easier for the optimiser? Should you do:
FROM table tableA AS A
JOIN tableB AS B
JOIN tableC AS C
JOIN tableD AS D
ON C.joinD = D.join
JOIN tableE AS E
ON c.joinE = E.join
ON B.joinC = C.join
ON A.joinB = B.join
OR
FROM table tableA AS A
JOIN tableB AS B
ON A.joinB = B.join
JOIN tableC AS C
ON B.joinC = C.join
JOIN tableD AS D
ON C.joinD = D.join
JOIN tableE AS E
ON c.joinE = E.join
OR is it immaterial and just a matter of style?
Regards,
Steve Tucknott
Attachment | Content-Type | Size |
---|---|---|
SQL | text/plain | 3.3 KB |
QUERYPLAN | text/plain | 15.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2006-01-02 14:49:32 | Re: backup by schema |
Previous Message | First Last | 2006-01-02 09:09:39 | can't log on to phppgadmin |