Hi, Josh.
Yes, I'd run a VACUUM FULL ANALYZE -- I did it again just to make sure,
and re-ran the query (similar result):
pganalysis=> explain analyze select * from ps2 where tstart> '2000-1-1
pganalysis'> 00:00:00' and time_stamp > '2000-1-1 00:00:00' order by
pganalysis-> tstart,time_stamp limit 59628;
NOTICE: QUERY PLAN:
Limit (cost=160313.27..160313.27 rows=59628 width=179) (actual
time=45405.47..46320.12 rows=59628 loops=1)
-> Sort (cost=160313.27..160313.27 rows=327895 width=179) (actual
time=45405.46..46248.31 rows=59629 loops=1)
-> Seq Scan on ps2 (cost=0.00..13783.40 rows=327895 width=179)
(actual time=13.52..17111.66 rows=327960 loops=1)
Total runtime: 46894.21 msec
EXPLAIN
Unfortunately, I have not yet had time to experiment with twiddling the
query optimizer parameters or memory -- my apologies for this, but,
well, a guy's gotta eat...
Regards,
Mike
On Wed, 2002-11-13 at 03:57, Josh Berkus wrote:
> Mike,
>
> > Given the estimated costs, PostgreSQL is doing the right things.
> >
> > However, in your case, it doesn't appear that the estimations are
> > realistic. Index scans are much cheaper than advertised.
>
> Can I assume that you've run VACUUM FULL ANALYZE on the table, or
> preferably the whole database?
>
> >
> > Try setting your random_page_cost lower (1.5 to 2 rather than 4).
> > Bumping sortmem to 32 or 64MB (if plenty of ram is available) will
> > help
> > most situations.
> >
> > Might see the 'pg_autotune' project for assistance in picking good
> > values.
> >
> > http://gborg.postgresql.org/project/pgautotune/projdisplay.php
>
> Um. I don't think we have anything to advertise yet, for pg_autotune.
> It's still very much an alpha, and the limits we set are pretty
> arbitrary.
>
> -Josh Berkus
--
Mike Nielsen <miken@bigpond.net.au>