Re: Slow query on OS X box

On Wed, 2003-01-22 at 13:26, Patrick Hatcher wrote:
> 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?

Yeah, you're pulling out 3.2 million rows from (possibly) a wide table
bytewise. Do all of those fields actually have data? Thats always
going to take a while -- and I find it hard to believe you're actually
doing something with all of those rows that runs regularly.

If every one of those rows was maxed out (ignoring the text field at the
end) you could have ~ 15GB of data to pull out. Without knowing the
type of data actually in the table, I'm going to bet it's a harddrive
limitation.

The index on 'wizard' is next to useless as at least 1/4 of the data in
the table is under the same key. You might try a partial index on
'wizard' (skip the value 'Keyword'). It won't help this query, but
it'll help ones looking for values other than 'Keyword'.

Anyway, you might try a CURSOR. Fetch rows out 5000 at a time, do some
work with them, then grab some more. This -- more or less -- will allow
you to process the rows received while awaiting the remaining lines to
be processed by the database. Depending on what you're doing with them
it'll give a chance for the diskdrive to catch up. If the kernels smart
it'll read ahead of the scan. This doesn't remove read time, but hides
it while you're transferring the data out (from the db to your client)
or processing it.

> 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)
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc