Did anyone have any ideas on this? If not, I'll look at rewriting the query completely.
On Mon, 2006-01-02 at 09:10 +0000, Steve Tucknott wrote:
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?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Regards,
Steve Tucknott
ReTSol Ltd
DDI: 01903 828769
Mobile: 0773 671 5772 |
