Re: 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: