Skip site navigation (1) Skip section navigation (2)

Re: Post-mature optimization...

From: Eric Walstad <eric(at)ericwalstad(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: Post-mature optimization...
Date: 2007-01-18 05:09:36
Message-ID: 45AF0110.6020707@ericwalstad.com (view raw or flat)
Thread:
Lists: sfpug
Hey Jeff,
Jeff Frost wrote:
> What does the explain output look like for this select:
> 
> SELECT * FROM foo  WHERE service_account NOT IN
>   (SELECT service_account FROM existing_data);
I'll change the actual table names to match my earlier example for
consistency.  Note that I left out the inner WHERE clause, originally,
to try to keep the example simple.  Here's the EXPLAIN:
=> EXPLAIN
->  SELECT * FROM foo
->  WHERE service_account NOT IN
->   (SELECT service_account FROM existing_data
(>    WHERE ut_id = 1 AND account_type = 'c');
                                        QUERY PLAN

------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..479764255675.94 rows=1137108 width=181)
   Filter: (NOT (subplan))
   SubPlan
     ->  Seq Scan on existing_data  (cost=0.00..413623.32 rows=3317084
width=13)
           Filter: ((ut_id = 1) AND ((account_type)::text = 'c'::text))
(5 rows)


> Also, how many service accounts are there in your real data?
=> SELECT COUNT(id) FROM uv2_serviceaddresses WHERE ut_id = 1 and
account_type='c';
  count
---------
 2274214
(1 row)

uv2=> SELECT COUNT(id) FROM uv2_serviceaddresses;
  count
---------
 8004888
(1 row)

Thanks for taking the time to respond,

Eric.

In response to

Responses

sfpug by date

Next:From: Eric WalstadDate: 2007-01-18 05:24:40
Subject: Re: VoIP recommendations
Previous:From: Reece HartDate: 2007-01-18 04:44:17
Subject: VoIP recommendations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group