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

Re: Post-mature optimization...

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Eric Walstad <eric(at)ericwalstad(dot)com>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Post-mature optimization...
Date: 2007-01-18 06:35:21
Message-ID: Pine.LNX.4.64.0701172221270.14094@discord.home.frostconsultingllc.com (view raw or flat)
Thread:
Lists: sfpug
On Wed, 17 Jan 2007, Eric Walstad wrote:

>> 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)
>

BTW, did you analyze after you created the index on foo (service_account) 
before running the above explain?  If not, could you analyze and then rerun 
the explain.  I have a feeling it won't help, but we can see if it does.

You might also do this and run explain again:

set enable_seqscan = off;

Just to make sure that using your index isn't helpful.  I have a feeling that 
you're using a large enough percentage of the table that a seqscan is faster 
than an index scan.

>
>> 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)

Actually, I was wondering what the output of the following might be:

SELECT distinct service_account FROM existing_data
     WHERE ut_id = 1 AND account_type = 'c';

I'm guessing that is the same as the output of the query below:

\>
> uv2=> SELECT COUNT(id) FROM uv2_serviceaddresses;
>  count
> ---------
> 8004888
> (1 row)
>
> Thanks for taking the time to respond,
>
> Eric.
>
>

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

In response to

sfpug by date

Next:From: Jeff FrostDate: 2007-01-18 06:37:42
Subject: Re: Post-mature optimization...
Previous:From: Quinn WeaverDate: 2007-01-18 06:16:35
Subject: Re: VoIP recommendations

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