Re: Slow query on OS X box
От | Rod Taylor |
---|---|
Тема | Re: Slow query on OS X box |
Дата | |
Msg-id | 1043265724.83856.147.camel@jester обсуждение исходный текст |
Ответ на | Slow query on OS X box ("Patrick Hatcher" <PHatcher@macys.com>) |
Список | pgsql-performance |
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@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Вложения
В списке pgsql-performance по дате отправления: