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@megazone23.bigpanda.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