Re: Sequential Scans On Complex Query With UNION - see why this fails - Mailing list pgsql-novice

Steve,

I don't have any explanation as to why your query is using an index
in one case and a sequential scan in another. However, I do have a
few observations after looking at your code that you might find helpful.

On Jan 2, 2006, at 3:10 , Steve Tucknott wrote:

> I am obviously doing something wrong in the structure of the query
> - any ideas?

When I first saw your FROM clause, I didn't even know that moving the
ON clause away from the JOIN it was related to would even work. I was
surprised to find that it does! I find it quite counterintuitive to
separate a join and its condition. As I understand it,

select *
from a
join b
join c on c.b_name = b.b_name
on b.a_name = a.a_name;

is just another way of writing

select *
from a
join b on b.a_name = a.a_name
join c on c.b_name = b.b_name;

and doesn't make a difference in query planning or performance. It's
definitely not 'hierarchical' as I understand it. Comparing EXPLAIN
ANALYZE output for the two query forms will show you if this is the
case.

In the queries you attached, I saw that you mix restrictions (e.g.,
c.b_name = 'some value') and join conditions (e.g., c.b_name =
b.b_name) in both the FROM clause and the WHERE clause. I personally
find this quite hard to read, though I don't think it makes much
difference to the server. I believe both join conditions and
restrictions are rewritten as they'd appear in the WHERE clause, so
you *could* put them all in the WHERE clause.

However, I find it useful to separate them out, and build queries
using JOIN ... ON (and actually I prefer the JOIN ... USING syntax
because only one set of join columns appears in the result). This
helps me clearly see how the tables are related. I then place all
restrictions in the WHERE clause. This helps me know where to look in
the query to find what I'm looking for. The optimizer should be able
to perform restrictions as necessary—not necessarily in the order the
original query was written. After all, that's the reason for its
existence :).

I also noticed was that you've used column numbers rather than column
names in your ORDER BY clause. I recommend using column names, as it
makes it easier to really see what you're ordering by without having
to refer to the SELECT target list. (In relational theory attribute
order does not matter. The SQL standard *does* refer to column order,
but you can go a long way and not have to worry about it, other than
for UNION compatibility, and even then, you don't need to use column
numbers explicitly.)

I've provided a rewritten query at the bottom of this post. I'd be
interested to see if it performs any differently that your original
query (e.g., how their EXPLAIN ANALYZE output compares).

The output also includes quite a few columns with duplicate column
names (e.g., clnt.name, clntB.name; srcD.recNo, clnt.recNo,
clntB.recNo, srcU.recNo). If feasible, I'd use AS to provide unique
column names, as this can make it easier to reference the columns
either in your client application or if you use this query as a
subquery in another statement. (I haven't done this below. There
could be a number of additional duplicates lurking in sProd.*,
prod.*, VAT.*, and mrkUP.* .)

What does the 1 = 1 do in your WHERE clause?

Also, the mixed case column and table names you use get lowered in
PostgreSQL, but you've probably already noticed that. To maintain
case, you need to double-quote these identifiers.

> Also,...as a quick 'method' question..when writing embedded joins,
> which syntax is easier for the optimiser? Should you do:

EXPLAIN ANALYZE will help you determine which form is more efficient
in your situation. Though as I've indicated above, I'd rewrite the
queries to make it easier for the programmer. Start by writing your
query as you think it *should* be written to make it easiest for
*you* to use. Then see how it performs. If you find it doesn't
perform well, you'll have a baseline against which you can compare
any improvement, and you'll learn what does and does not work. The
optimizer is pretty good—don't second guess it. Code should be easy
for the programmer to read and use first.

Hope this helps!

Michael Glaesemann
grzm myrealbox com

--- original query

--- rewritten query
SELECT clnt.name
     , clntB.name
     , periodOf(pOHdr.orderDate)
     , srcD.recNo
     , pODet.purchaseOrdHdrRecNo
     , clnt.recNo
     , clntB.recNo
     , srcU.recNo, srcU.replacementCost, srcU.requiredQty
     , sProd.*
     , prod.*
     , VAT.*
     , pODet.orderedQty
     , mrkUp.*
FROM purchaseOrdHdr AS pOHdr
JOIN purchaseOrdDet AS pODet ON (pODet.purchaseOrdHdrRecNo =
pOHdr.recNo)
JOIN sourceDetUpgr AS srcU ON (srcU.recNo = pODet.sourceDetUpgrRecNo)
JOIN sourceDet AS srcD ON (srcD.recNo = srcU.sourceDetRecNo)
JOIN sourceDetExtref AS srcE ON (srcE.foreignRecNo = srcD.recNo)
JOIN clientBranch AS clntB ON (clntB.recNo = srcE.ownerForeignRecNo)
JOIN client AS clnt ON (clntB.clientRecNo = clnt.recNo)
JOIN markUp AS mrkUp ON (mrkUp.foreignRecNo = clnt.recNo)
JOIN supplierProduct AS sProd ON (sProd.recNo =
srcU.supplierProductRecNo)
JOIN product AS prod ON (prod.recNo = sProd.productRecNo)
JOIN VAT AS VAT ON (sProd.VATRecNo = VAT.recNo)
WHERE pOHdr.orderDate BETWEEN '01/12/2005' AND '21/12/2005'
     AND  1=1 -- what does this do?
     AND pODet.lineStatus != 'V'
     AND srcD.actionStatus != 'V'
     AND srcD.serviceCoBranchRecNo =  2
     AND srcU.lineStatus != 'V'
     AND srcE.tableName = 'sourcedet' -- moved down from join condition
     AND srcE.ownerForeignTableName = 'clientbranch' -- moved down
from join condition
     AND mrkUp.foreignTableName = 'client' -- moved down from join
condition

UNION

SELECT clnt.name
     , clntB.name
     , periodOf(srcD.enteredDate)
     , srcD.recNo
     , NULL
     , clnt.recNo
     , clntB.recNo
     , srcU.recNo, srcU.replacementCost, srcU.requiredQty
     , sProd.*
     , prod.*
     , VAT.*
     , 0
     , mrkUp.*
FROM sourceDet AS srcD
JOIN sourceDetUpgr AS srcU ON (srcU.sourceDetRecNo = srcD.recNo)
JOIN supplierProduct AS sProd ON (srcU.supplierProductRecNo =
sProd.recNo)
JOIN product AS prod ON (sProd.productRecNo = prod.recNo)
JOIN VAT AS VAT ON (sProd.VATRecNo = VAT.recNo)
JOIN sourceDetExtref AS srcE ON (srcE.foreignRecNo = srcD.recNo)
JOIN clientBranch AS clntB ON (clntB.recNo = srcE.ownerForeignRecNo)
JOIN client AS clnt ON (clntB.clientRecNo = clnt.recNo)
JOIN markUp AS mrkUp ON (mrkUp.foreignRecNo = clnt.recNo)
WHERE srcD.enteredDate BETWEEN '01/12/2005' AND '21/12/2005'
     AND  srcD.serviceCoBranchRecNo = 2
     AND srcD.actionStatus != 'V'
     AND srcU.lineStatus = 'S'
     AND mrkUp.foreignTableName = 'client' -- from join condition
     AND srcE.tableName = 'sourcedet' -- from join condition
     AND srcE.ownerForeignTableName = 'clientbranch' -- from join
condition
     AND (
             SELECT COUNT(*)
             FROM sourceDetUpgr AS srcU2
             WHERE srcU2.sourceDetRecNo = srcD.recNo
                 AND srcU2.lineStatus IN ('S','T')
         ) = (
             SELECT COUNT(*)
             FROM sourceDetUpgr AS srcU2
             WHERE srcU2.sourceDetRecNo = srcD.recNo
                 AND srcU2.lineStatus != 'V'
         )
AND  1=1 -- what does this do?
ORDER BY  --3,2,1,4,5
     periodof
     , clntB.name
     , clnt.name
     , srD.recNo
     , pODet.purchaseOrdHdrRecNo


--- original query
SELECT clnt.name,clntB.name,periodOf
(pOHdr.orderDate),srcD.recNo,pODet.purchaseOrdHdrRecNo,clnt.recNo,clntB.
recNo,srcU.recNo,srcU.replacementCost,srcU.requiredQty,sProd.*,prod.*,VA
T.*,pODet.orderedQty,mrkUp.*
FROM purchaseOrdHdr AS pOHdr
     JOIN purchaseOrdDet AS pODet
          JOIN sourceDetUpgr AS srcU
               JOIN sourceDet AS srcD
                    JOIN sourceDetExtref AS srcE
                         JOIN clientBranch AS clntB
                              JOIN client AS clnt
                                   JOIN markUp AS mrkUp
                                   ON  mrkUp.foreignRecNo = clnt.recNo
                                   AND mrkUp.foreignTableName = 'client'
                              ON  clntB.clientRecNo   = clnt.recNo
                         ON  clntB.recNo          =
srcE.ownerForeignRecNo
                    ON  srcE.foreignRecNo           = srcD.recNo
                    AND srcE.tableName              = 'sourcedet'
                    AND srcE.ownerForeignTableName  = 'clientbranch'
               ON  srcD.recNo = srcU.sourceDetRecNo
               JOIN supplierProduct AS sProd
                    JOIN product AS prod
                    ON prod.recNo  = sProd.productRecNo
                       JOIN VAT AS VAT
                       ON  sProd.VATRecNo          = VAT.recNo
               ON  sProd.recNo     = srcU.supplierProductRecNo
          ON  srcU.recNo       = pODet.sourceDetUpgrRecNo
     ON  pODet.purchaseOrdHdrRecNo    = pOHdr.recNo
WHERE pOHdr.orderDate BETWEEN '01/12/2005' AND '21/12/2005'
AND  1=1
AND pODet.lineStatus            != 'V'
AND srcD.actionStatus           != 'V'
AND srcD.serviceCoBranchRecNo    =  2
AND srcU.lineStatus         != 'V'
UNION
SELECT clnt.name,clntB.name,periodOf
(srcD.enteredDate),srcD.recNo,NULL,clnt.recNo,clntB.recNo,srcU.recNo,
        srcU.replacementCost,srcU.requiredQty,sProd.*,prod.*,VAT.*,
0,mrkUp.*
FROM sourceDet AS srcD
      JOIN sourceDetUpgr AS srcU
           JOIN supplierProduct AS sProd
                JOIN product AS prod
                ON sProd.productRecNo       = prod.recNo
                JOIN VAT AS VAT
                ON  sProd.VATRecNo          = VAT.recNo
           ON  srcU.supplierProductRecNo    = sProd.recNo
      ON  srcU.sourceDetRecNo   = srcD.recNo
      JOIN sourceDetExtref AS srcE
           JOIN clientBranch AS clntB
                JOIN client AS clnt
                     JOIN markUp AS mrkUp
                     ON  mrkUp.foreignRecNo = clnt.recNo
                     AND mrkUp.foreignTableName = 'client'
                ON  clntB.clientRecNo   = clnt.recNo
           ON  clntB.recNo              = srcE.ownerForeignRecNo
      ON  srcE.foreignRecNo           = srcD.recNo
      AND srcE.tableName              = 'sourcedet'
      AND srcE.ownerForeignTableName  = 'clientbranch'
WHERE srcD.enteredDate BETWEEN '01/12/2005' AND '21/12/2005'
AND  srcD.serviceCoBranchRecNo    = 2
AND   srcD.actionStatus            != 'V'
AND   srcU.lineStatus           = 'S'
AND   (SELECT COUNT(*)  FROM sourceDetUpgr AS srcU2
        WHERE srcU2.sourceDetRecNo = srcD.recNo
        AND   srcU2.lineStatus IN ('S','T')) = (SELECT COUNT(*) FROM
sourceDetUpgr AS srcU2
                                                WHERE
srcU2.sourceDetRecNo = srcD.recNo
                                                AND
srcU2.lineStatus != 'V')
AND  1=1
ORDER BY 3,2,1,4,5