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:
