From: | Steve Heaven <steve(at)thornet(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Query plan and sub-queries |
Date: | 2000-08-08 11:22:12 |
Message-ID: | 3.0.1.32.20000808122212.00bb1c2c@mail.thornet.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
When the WHERE clause includes a sub query the query plan seems to ignore
indexes.
See the examples below.
Table R1684 has one column, stockno, which is the same type as the stockno
in the books_fti table. There is no index on R1684.
In the first case the index on books_fti(stockno) is not used but in the
second case it is.
=============================== Query 1
=======================================
explain select * from books_fti where stockno in (select stockno from R1684);
Seq Scan on books_fti (cost=79300.27 rows=1024705 width=160)
SubPlan
-> Seq Scan on r1684 (cost=43.00 rows=1000 width=12)
================================ Query 2
=======================================
explain select * from books_fti where stockno in
('0815171161','1857281012','0419251901');
Index Scan using allbooks_isbn, allbooks_isbn, allbooks_isbn on books_fti
(cost
=6.15 rows=5 width=160)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
From | Date | Subject | |
---|---|---|---|
Next Message | Robert D. Nelson | 2000-08-08 12:18:00 | RE: pg_dump help |
Previous Message | Len Morgan | 2000-08-08 05:56:16 | Fw: pg_dump help -- Problem Fixed -- Thank you all!!! |
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Mascari | 2000-08-08 12:24:28 | Re: Query plan and sub-queries |
Previous Message | Karel Zak | 2000-08-08 08:26:06 | Re: Constraint stuff |