Re: Slow query needs a kick in the pants.

This is the SQL Server plan for the same query. It may be worth
studying, since it is absurdly faster than what PostgreSQL does.

StmtText
StmtId NodeId Parent PhysicalOp
LogicalOp Argument
DefinedValues
EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList
Warnings Type Parallel EstimateExecutions

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------- ----------- -----------
----------- ------------------------------
------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------
------------------------------------------------------------------
------------------------ ------------------------
------------------------ ----------- ------------------------
------------------------------------------------------------------
-------- ------------------------------ --------
------------------------
select a.crc from CNX_DS2_1_BCHFIL_FILE a left outer join
CNX_DS_1_BCHFIL_FILE b on ( a.FILE_KEY = b.FILE_KEY and a.SYS_YYYYMMDD =
b.SYS_YYYYMMDD and a.SYS_HHMMSSUU = b.SYS_HHMMSSUU ) where ( b.crc is
NULL ) 4 1
0 NULL NULL
NULL
NULL 1.0
NULL NULL NULL 121.25754
NULL NULL
SELECT 0 NULL
|--Filter(WHERE:([b].[crc]=NULL))
4 3 1 Filter
Filter WHERE:([b].[crc]=NULL)
NULL 1.0
0.0 0.24271794 125 121.25754
[a].[crc] NULL
PLAN_ROW 0 1.0
|--Hash Match(Left Outer Join, HASH:([a].[FILE_KEY],
[a].[SYS_HHMMSSUU], [a].[SYS_YYYYMMDD])=([b].[FILE_KEY],
[b].[SYS_HHMMSSUU], [b].[SYS_YYYYMMDD]),
RESIDUAL:(([a].[FILE_KEY]=[b].[FILE_KEY] AND
[a].[SYS_HHMMSSUU]=[b].[SYS_HHMMSSUU]) AND [a].[SYS_Y 4 4
3 Hash Match Left Outer Join
HASH:([a].[FILE_KEY], [a].[SYS_HHMMSSUU],
[a].[SYS_YYYYMMDD])=([b].[FILE_KEY], [b].[SYS_HHMMSSUU],
[b].[SYS_YYYYMMDD]), RESIDUAL:(([a].[FILE_KEY]=[b].[FILE_KEY] AND
[a].[SYS_HHMMSSUU]=[b].[SYS_HHMMSSUU]) AND
[a].[SYS_YYYYMMDD]=[b].[SYS_YYYYMMDD]) NULL
1348433.0 42.871052 60.077095
125 120.61029 [a].[crc], [b].[crc]
NULL PLAN_ROW 0 1.0
|--Table
Scan(OBJECT:([Scratch].[dbo].[CNX_DS2_1_BCHFIL_FILE] AS [a]))
4 5 4 Table Scan Table
Scan OBJECT:([Scratch].[dbo].[CNX_DS2_1_BCHFIL_FILE]
AS [a])
[a].[crc], [a].[FILE_KEY], [a].[SYS_HHMMSSUU], [a].[SYS_YYYYMMDD]
973970.0 7.7205415 1.0714455
77 8.7919874 [a].[crc], [a].[FILE_KEY],
[a].[SYS_HHMMSSUU], [a].[SYS_YYYYMMDD] NULL PLAN_ROW
0 1.0
|--Table Scan(OBJECT:([Scratch].[dbo].[CNX_DS_1_BCHFIL_FILE]
AS [b]))
4 6 4 Table Scan Table
Scan OBJECT:([Scratch].[dbo].[CNX_DS_1_BCHFIL_FILE]
AS [b])
[b].[crc], [b].[FILE_KEY], [b].[SYS_HHMMSSUU], [b].[SYS_YYYYMMDD]
983068.0 7.7886896 1.0814533
77 8.8701429 [b].[crc], [b].[FILE_KEY],
[b].[SYS_HHMMSSUU], [b].[SYS_YYYYMMDD] NULL PLAN_ROW
0 1.0

> -----Original Message-----
> From: Dann Corbit
> Sent: Friday, March 28, 2003 10:55 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: RE: [GENERAL] Slow query needs a kick in the pants.
>
>
> Food for optimizer thought...
>
> The same query, ported to SQL*Server, is 20x faster than PostgreSQL.
>
> -----Original Message-----
> From: Dann Corbit
> Sent: Thursday, March 27, 2003 3:30 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Slow query needs a kick in the pants.
>
>
> This query:
>
> connxdatasync=# select "a".OID
> connxdatasync-# from "CNX_DS2_1_BCHFIL_FILE" "a"
> connxdatasync-# left outer join "CNX_DS_1_BCHFIL_FILE" "b" on
> connxdatasync-# ( "a"."FILE_KEY" = "b"."FILE_KEY" and
> connxdatasync(# "a"."SYS_YYYYMMDD" =
> "b"."SYS_YYYYMMDD" and
> connxdatasync(# "a"."SYS_HHMMSSUU" =
> "b"."SYS_HHMMSSUU" )
> connxdatasync-# where ( "b".OID is NULL )
> connxdatasync-# ;
>
> Is abysmally slow.
>
> connxdatasync=# \d "CNX_DS2_1_BCHFIL_FILE"
> Table "CNX_DS2_1_BCHFIL_FILE"
> Attribute | Type | Modifier
> --------------+---------------+----------
> FILE_KEY | character(30) |
> SYS_YYYYMMDD | character(8) |
> SYS_HHMMSSUU | character(8) |
> CRC | bigint | not null
> Index: UA4IYKF5LY9402
>
> connxdatasync=# explain VERBOSE
> connxdatasync-# select "a".OID
> connxdatasync-# from "CNX_DS2_1_BCHFIL_FILE" "a"
> connxdatasync-# left outer join "CNX_DS_1_BCHFIL_FILE" "b" on
> connxdatasync-# ( "a"."FILE_KEY" = "b"."FILE_KEY" and
> connxdatasync(# "a"."SYS_YYYYMMDD" =
> "b"."SYS_YYYYMMDD" and
> connxdatasync(# "a"."SYS_HHMMSSUU" =
> "b"."SYS_HHMMSSUU" )
> connxdatasync-# where ( "b".OID is NULL )
> connxdatasync-# ;
> NOTICE: QUERY DUMP:
>