pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: csajl <csajl@yahoo.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: [repost] partial index / funxtional idx or bad sql?
Date: Mon, 12 May 2003 21:03:38 -0700 (PDT)
Message-ID: <20030513040338.3925.qmail@web40305.mail.yahoo.com> (raw)
In-Reply-To: <20030512204533.I20042-100000@megazone23.bigpanda.com>


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




view thread (10+ messages)  latest in thread

Message-ID: <20030513040338.3925.qmail@web40305.mail.yahoo.com>
Permalink:  ../20030513040338.3925.qmail@web40305.mail.yahoo.com/
Also on:    postgresql.org/message-id/20030513040338.3925.qmail@web40305.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: csajl@yahoo.com, sszabo@megazone23.bigpanda.com, josh@agliodbs.com
  Subject: Re: [repost] partial index / funxtional idx or bad sql?
  In-Reply-To: <20030513040338.3925.qmail@web40305.mail.yahoo.com>
  References: <20030512204533.I20042-100000@megazone23.bigpanda.com> <20030513040338.3925.qmail@web40305.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox