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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Frost | 2007-01-18 06:37:42 | Re: Post-mature optimization... |
Previous Message | Quinn Weaver | 2007-01-18 06:16:35 | Re: VoIP recommendations |