Re: [repost] partial index / funxtional idx or bad sql?

wow.

that did it. so much for my knowing SQL...

unbelievable - thanks much.

cmdb=# EXPLAIN ANALYZE
cmdb-# select c.class_id, c.areacode, c.title from classifieds c
cmdb-# , (select distinct areacode from cm_areacode where site_id='10') a
cmdb-# where c.class_cat_id='1' and c.areacode=a.areacode;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=7.44..1107.53 rows=279 width=41) (actual time=1.13..258.11
rows=8460 loops=1)
-> Subquery Scan a (cost=7.44..7.46 rows=1 width=2) (actual
time=0.86..0.92 rows=5 loops=1)
-> Unique (cost=7.44..7.46 rows=1 width=2) (actual time=0.85..0.88
rows=5 loops=1)
-> Sort (cost=7.44..7.45 rows=4 width=2) (actual
time=0.85..0.86 rows=5 loops=1)
Sort Key: areacode
-> Seq Scan on cm_areacode (cost=0.00..7.40 rows=4
width=2) (actual time=0.20..0.73 rows=5 loops=1)
Filter: (site_id = 10)
-> Index Scan using classifieds_dual_idx on classifieds c
(cost=0.00..1096.59 rows=279 width=39) (actual time=0.22..44.28 rows=1692
loops=5)
Index Cond: ((c.class_cat_id = 1) AND (c.areacode = "outer".areacode))
Total runtime: 267.71 msec
(10 rows)

--- Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
>
> On Mon, 12 May 2003, csajl wrote:
>
> > i'm using 7.3.2. i tried using EXISTS instead of the IN, but the same
> query
> > now returns in seven sceonds as opposed to four with the IN.
> >
> >
> > cmdb=# EXPLAIN ANALYZE
> > cmdb-# select c.class_id, c.areacode, c.title from classifieds c
> > cmdb-# where c.class_cat_id = '1'
> > cmdb-# and c.areacode IN (
> > cmdb(# select areacode from cm_areacode where site_id = '10')
> > cmdb-# ;
>
> How about something like:
>
> select c.class_id, c.areacode, c.title from
> classifieds c,
> (select distinct areacode from cm_areacode where site_id='10') a
> where c.class_cat_id='1' and c.areacode=a.areacode;
>

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com