What's happening here?
I have two tables, encounter_properties_table with about 100000 rows and
xfiles with about 500000 rows. The structures of these tables is as follows:
Table "public.encounter_properties_table"
Column | Type | Modifiers
----------------+--------------------------+-----------
timestamp | timestamp with time zone | not null
practice_id | integer | not null
patient_id | bigint | not null
properties | text |
modified_by | bigint | not null
client_version | integer |
file_name | character varying(255) |
Indexes:
"encounter_properties_table_pkey" primary key, btree (patient_id)
"fn_ix" btree (file_name)
and
Table "public.xfiles"
Column | Type | Modifiers
----------+------------------------+-----------
filename | character varying(100) | not null
Indexes:
"xfiles_ix1" btree (filename)
The following query shows that PostgreSQL 7.4 is doing table scans on both
tables:
explain select file_name from encounter_properties_table where file_name not
in (select filename from xfiles);
QUERY PLAN
----------------------------------------------------------------------------
------------
Seq Scan on encounter_properties_table (cost=0.00..1030610198.10
rows=85828 width=58)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on xfiles (cost=0.00..10755.44 rows=500944 width=59)
(4 rows)
I ran vacumm analyze on both tables.
We aborted this query when it had not finished after 4 hours.
We ran the same query on SQLServer 2005 with the same data and it took under
one second to finish.
Any ideas?
BEGIN:VCARD VERSION:2.1 N:Kuntz;G. Ralph FN:G. Ralph Kuntz (grk@xxxxxxx) ORG:meridianEMR, Inc TITLE:Chief Software Architect TEL;WORK;VOICE:(973) 994-3220 TEL;HOME;VOICE:(973) 989-4392 TEL;CELL;VOICE:(973) 214-4464 TEL;WORK;FAX:(973) 994-0027 ADR;WORK:;;354 Eisenhower Parkway;Livingston;NJ;07039;United States LABEL;WORK;ENCODING=QUOTED-PRINTABLE:354 Eisenhower Parkway=0D=0ALivingston, NJ 07039=0D=0AUnited States EMAIL;PREF;INTERNET:grk@xxxxxxx REV:20051130T173408Z END:VCARD
Attachment:
PGP.sig
Description: PGP signature
