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