Re: Slow query needs a kick in the pants. - Mailing list pgsql-general

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@postgresql.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@postgresql.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:
>