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?