Re: Post-mature optimization...

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