Thread: Query plan and sub-queries
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
Steve Heaven wrote:
>
> When the WHERE clause includes a sub query the query plan seems to ignore
> indexes.
This is a FAQ:
4.23) Why are my subqueries using IN so slow?
Currently, we join subqueries to outer queries by sequential
scanning the result of the subquery for each row of the outer
query. A workaround is to replace IN with EXISTS:
SELECT *
FROM tab
WHERE col1 IN (SELECT col2 FROM TAB2)
to:
SELECT *
FROM tab
WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
We hope to fix this limitation in a future release.
Hope that helps,
Mike Mascari
At 08:24 08/08/00 -0400, you wrote:
> A workaround is to replace IN with EXISTS:
This still does a sequential rather that indexed scan:
explain select * from books_fti where exists
(select R1684.stockno from R1684,books_fti where
R1684.stockno=books_fti.stockno );
Result (cost=79300.27 rows=0 width=0)
InitPlan
-> Nested Loop (cost=2093.00 rows=1024706 width=24)
-> Seq Scan on r1684 (cost=43.00 rows=1000 width=12)
-> Index Scan using allbooks_isbn on books_fti (cost=2.05
rows=1024705 width=12)
-> Seq Scan on books_fti (cost=79300.27 rows=1024705 width=160)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
Steve Heaven wrote:
>
> At 08:24 08/08/00 -0400, you wrote:
> > A workaround is to replace IN with EXISTS:
>
> This still does a sequential rather that indexed scan:
>
> explain select * from books_fti where exists
> (select R1684.stockno from R1684,books_fti where
> R1684.stockno=books_fti.stockno );
Firstly, a simple join would yield the same results:
SELECT books_fti.* FROM books_fti, R1684 WHERE
books_fti.stockno = R1684.stockno;
Secondly, you've listed the target table twice in the above
query, which might be causing a problem with the planner.
Instead, it should read:
SELECT * FROM books_fti WHERE EXISTS (
SELECT R1684.stockno FROM R1684 WHERE R1684.stockno =
books_fti.stockno
);
That should result in 1 sequential scan on one of the tables, and
1 index scan on the inner table. The plan should look something
like:
Seq Scan on R1684 (cost=9.44 rows=165 width=12)
SubPlan
-> Index Scan using allbooks_isbn on books_fti (cost=490.59
rows=7552 width=12)
Hope that helps,
Mike Mascari
At 10:17 08/08/00 -0400, Mike Mascari wrote:
>
>Firstly, a simple join would yield the same results:
>
>SELECT books_fti.* FROM books_fti, R1684 WHERE
>books_fti.stockno = R1684.stockno;
Yes that gives me:
Nested Loop (cost=2093.00 rows=1024706 width=172)
-> Seq Scan on r1689 (cost=43.00 rows=1000 width=12)
-> Index Scan using allbooks_isbn on books_fti (cost=2.05 rows=1024705
width
=160)
But the 'EXISTS' sub-query you suggest still doesnt use the index.
>SELECT * FROM books_fti WHERE EXISTS (
> SELECT R1684.stockno FROM R1684 WHERE R1684.stockno =
>books_fti.stockno
>);
>
>That should result in 1 sequential scan on one of the tables, and
>1 index scan on the inner table. The plan should look something
>like:
>
>Seq Scan on R1684 (cost=9.44 rows=165 width=12)
> SubPlan
> -> Index Scan using allbooks_isbn on books_fti (cost=490.59
>rows=7552 width=12)
>
No actually I'm getting:
Seq Scan on books_fti (cost=79300.27 rows=1024705 width=160)
SubPlan
-> Seq Scan on r1684 (cost=43.00 rows=2 width=12)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
Has something happened to the list server ? I am only subscribed to the general list, but after two days of nothing I'm now getting the hackers list stuff. Steve -- thorNET - Internet Consultancy, Services & Training Phone: 01454 854413 Fax: 01454 854412 http://www.thornet.co.uk
> Has something happened to the list server ? > > I am only subscribed to the general list, but after two days of nothing I'm > now getting the hackers list stuff. > So it's not just me? How sad, I was hoping I had be promoted to Hacker status... ;-) /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 faermini@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it
okay, this is most odd ... according to the list software, you are still
only subscribed to the general list:
Address: fabrizio.ermini@sysdat.it
Address is valid.
Address is registered as:
fabrizio.ermini@sysdat.it
Registered at Fri Sep 1 15:33:13 2000 GMT.
Registration data last changed at Fri Sep 1 15:33:13 2000 GMT.
Address is subscribed to 1 list:
pgsql-general:
Subscribed at Fri Sep 1 15:33:13 2000 GMT.
Receiving each message as it is posted.
Subscriber flags:
noeliminatecc
nohide
prefix
replyto
selfcopy
norewritefrom
noackstall
noackdeny
noackpost
noackreject
Data last changed at Fri Sep 1 15:33:13 2000 GMT.
can you forward me a copy of the next 'hackers' message you receive, along
with its *full* headers? Just to make sure, pgsql-general@postgresql.org
hasn't been inadvertently subscribed to hackers, so we aren't getting a
cross there:
Majordomo>show pgsql-general@postgresql.org
Address: pgsql-general@postgresql.org
Address is valid.
Address is not registered.
On Thu, 14 Sep 2000 fabrizio.ermini@sysdat.it wrote:
> > Has something happened to the list server ?
> >
> > I am only subscribed to the general list, but after two days of nothing I'm
> > now getting the hackers list stuff.
> >
> So it's not just me?
>
> How sad, I was hoping I had be promoted to Hacker status... ;-)
>
>
> /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
>
> Fabrizio Ermini Alternate E-mail:
> C.so Umberto, 7 faermini@tin.it
> loc. Meleto Valdarno Mail on GSM: (keep it short!)
> 52020 Cavriglia (AR) faermini@sms.tin.it
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
When last we left our intrepid adventurers... > Has something happened to the list server ? > > I am only subscribed to the general list, but after two days of nothing I'm > now getting the hackers list stuff. > >So it's not just me? It's not just you... this morning, I was surprised that my filters hadn't filtered the pgsql-hackers messages to another folder, when I realized, HEY! I'm not ON the hackers list... So for lack of anything better to to, I unsubbed, and got a return message that it was successful. This, in spite of the fact that I'd never subscribed. Hmmm... Now, back to our regularly scheduled programming. David Veatch - dvicci@reckoning.org "Many people would sooner die than think. In fact, they do." - Bertrand Russell
On Thu, Sep 14, 2000 at 09:01:50AM -0300, The Hermit Hacker wrote: > > okay, this is most odd ... according to the list software, you are still > only subscribed to the general list: Marc I can also confirm that I had no message on pgsql-general for about two days until the thread 'List Funnies' started. Some -general has been vanishing into a black hole. (Including one message I know a friend of mine, 'Richard Poole <richard.poole@vi.net>' sent recently). Jules
there was a problem with database corruption in pgsql-general that we
fixed last night ... if anyone else is interested in helping, I'm going to
be working with the Mj2 guys on moving the backend from BerkeleyDB ->
PostgreSQL ... if anyone is interested in helping out, let me know ...
On Thu, 14 Sep 2000, Jules Bean wrote:
> On Thu, Sep 14, 2000 at 09:01:50AM -0300, The Hermit Hacker wrote:
> >
> > okay, this is most odd ... according to the list software, you are still
> > only subscribed to the general list:
>
> Marc
>
> I can also confirm that I had no message on pgsql-general for about
> two days until the thread 'List Funnies' started. Some -general has
> been vanishing into a black hole. (Including one message I know a
> friend of mine, 'Richard Poole <richard.poole@vi.net>' sent recently).
>
> Jules
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org