Sequential Scans On Complex Query With UNION - see why this fails

Поиск
Список
Период
Сортировка
От Steve Tucknott
Тема Sequential Scans On Complex Query With UNION - see why this fails
Дата
Msg-id 1136193045.4179.1.camel@retsol1
обсуждение исходный текст
Ответы Re: Sequential Scans On Complex Query With UNION - see  (Steve Tucknott <steve@retsol.co.uk>)
Re: Sequential Scans On Complex Query With UNION - see why this fails  (Michael Glaesemann <grzm@myrealbox.com>)
Список 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




Вложения

В списке pgsql-novice по дате отправления:

Предыдущее
От: John DeSoi
Дата:
Сообщение: Re: array size problem
Следующее
От: First Last
Дата:
Сообщение: can't log on to phppgadmin