Re: Slow query on OS X box

"Patrick Hatcher" <PHatcher(at)macys(dot)com> writes:
> I have a table that contains over 13 million rows. This query takes an
> extremely long time to return. I've vacuum full, analyzed, and re-indexed
> the table. Still the results are the same. Any ideas?

> mdc_oz=# explain analyze select wizard from search_log where wizard
> ='Keyword' and sdate between '2002-12-01' and '2003-01-15';
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
> Seq Scan on search_log (cost=0.00..609015.34 rows=3305729 width=10)
> (actual time=99833.83..162951.25 rows=3280573 loops=1)
> Filter: ((wizard = 'Keyword'::character varying) AND (sdate >
> = '2002-12-01'::date) AND (sdate <= '2003-01-15'::date))
> Total runtime: 174713.25 msec
> (3 rows)

This query is selecting 3280573 rows out of your 13 million. I'd say
the machine is doing the best it can. Returning 19000 rows per second
is not all that shabby.

Perhaps you should rethink what you're doing. Do you actually need to
return 3 million rows to the client?

regards, tom lane