Re: nested select query failing

From: "amol" <amol(at)mithi(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: nested select query failing
Date: 2003-05-20 07:26:16
Message-ID: 00a101c31ea1$19e03d80$2e00a8c0@amol
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

thanks allot everybody for your mails,

- It helped and now I have got down the query execution time allot. But I am
facing problem in following query
-----------
explain analyze select attached_info.id from attached_tag_list,
attached_info
where
attached_tag_list.attached_tag = 265
and
attached_tag_list.id = attached_info.id
----------

- it's result is
----------
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..165349.50 rows=114 width=16) (actual
time=117.14..8994.60 rows=15 loops=1)
-> Index Scan using ix_attached_tag_list_id on attached_tag_list
(cost=0.00..111.13 rows=96 width=12) (actual time=0.12..0.66 rows=15
loops=1)
-> Seq Scan on attached_info (cost=0.00..1211.53 rows=33553 width=4)
(actual time=3.67..197.98 rows=33553 loops=15)
Total runtime: 8994.92 msec

EXPLAIN
---------

- I have already indexed attached_info on id using following query
------
CREATE INDEX attached_info_Index_1 ON attached_info(id) ;
------

- But I am wondering why there is "->Seq Scan on attached_info."
After reading various documentation on the internet I am assuming it
should have been an index scan. BTW I have done vaccume analyze also.

Am I right?

thanks,
Amol

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: "SZUCS Gábor" <surrano(at)mailbox(dot)hu>
Cc: <pgsql-performance(at)postgresql(dot)org>
Sent: Thursday, May 15, 2003 8:26 PM
Subject: Re: [PERFORM] nested select query failing

On Thu, 15 May 2003, [iso-8859-1] SZUCS Gábor wrote:

> Basically, is it true that IN's can be converted to RIGHT JOIN's quite
> simply? Is it always worth?

I'm not sure you want to convert to an outer join (since you want to throw
away the rows on either side that don't match in an IN). You also have to
be careful not to get duplicate entries from what was the subquery.

As for whether it's worth doing, in 7.3 and earlier, almost
certainly, in 7.4 almost certainly not. :)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Hannu Krosing 2003-05-20 08:29:34 Re: postgres on a beowulf? (AMD)opteron?
Previous Message Shridhar Daithankar 2003-05-20 06:44:03 Re: postgres on a beowulf? (AMD)opteron?