Re: Sequential Scans On Complex Query With UNION - see

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?
>
> Regards,
>
> Steve Tucknott
>
>
>
>
>
>
>
> ---------------------------(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