Обсуждение: 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