Query plan and sub-queries

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

I was just about to ask the same question...

med v�nlig h�lsning
/Dana

-----Original Message-----
From: steve@thornet.co.uk [SMTP:steve@thornet.co.uk]
Sent: Thursday, September 14, 2000 10:09 AM
To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org
Subject: [HACKERS] List funnies ?

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

Me too, and I'm getting some stuff two times, like I'm double subscribed
(which I shouldn't be). Sometimes it takes few days for messages to appear
on list etc.

At 10:12 14.9.2000 , Dana.Reed@clinicaldatacare.com wrote:

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

removed

On Thu, 14 Sep 2000, Zeljko Trogrlic wrote:

Me too, and I'm getting some stuff two times, like I'm double subscribed
(which I shouldn't be). Sometimes it takes few days for messages to appear
on list etc.

At 10:12 14.9.2000 , Dana.Reed@clinicaldatacare.com wrote:

I was just about to ask the same question...

med v���nlig h���lsning
/Dana

-----Original Message-----
From: steve@thornet.co.uk [SMTP:steve@thornet.co.uk]
Sent: Thursday, September 14, 2000 10:09 AM
To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org
Subject: [HACKERS] List funnies ?

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

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

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

Thanks!

At 14:39 14.9.2000 , The Hermit Hacker wrote:

Forgive me if I am missing the obvious, but can someone please tell me how
to show a list of triggers, or the code in a specific trigger, via the pgsql
utility?

Thank you,
Bryan

You can get a list of all triggers in the system
as
select * from pg_trigger;

You can get the source for a PL function a trigger
calls with:
select prosrc from pg_trigger,pg_proc where
pg_proc.oid=pg_trigger.tgfoid
and pg_trigger.tgname = '<name>'

[Note, in the case of C functions, I think this
returns the name of the function.]

Stephan Szabo
sszabo@bigpanda.com

On Thu, 14 Sep 2000, Bryan Field-Elliot wrote:

I've got a database I would like to upgrade from 6.3.2+phpfi 2.0 to
7.0.2+php 4.0. I've found the changes needed to be made to the php code,
and that should not be a problem. The real issue I'm having at this point
is getting the data into 7.0.2, or any newer release of PostgreSQL. I've
tried the pg_upgrade, but it does not seem to work for 6.3.2 -> 6.5.3.
Should I try 6.4 first, then 6.5.3, then 7.0? I've tried pg_dump and
pg_dumpall, but the data will not insert into the newer version
database. Do I need to provide some err info here? What would be helpful?

Notes on this setup: I've inherited the DBA position for this database. I
know the people who designed and built it, but cannot get any help from
them. They added some special charactor types and some compiled C code to
the postgres install, and I think that may be what's throwing the process
out. Any suggestions?

Thank you,

Chris Sterling
lemmy@eaze.net