Q: Table scans on set difference
От | G. Ralph Kuntz, MD |
---|---|
Тема | Q: Table scans on set difference |
Дата | |
Msg-id | 004d01c6a69d$5ffa6700$649617ac@predator обсуждение исходный текст |
Ответы |
Re: Q: Table scans on set difference
|
Список | pgsql-general |
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?
Вложения
В списке pgsql-general по дате отправления: