From: | Mike Nielsen <miken(at)bigpond(dot)net(dot)au> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Rod Taylor <rbt(at)rbt(dot)ca>, Postgresql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Query performance discontinuity |
Date: | 2002-11-13 00:04:01 |
Message-ID: | 1037145841.2280.7.camel@CPE-144-132-182-167.nsw.bigpond.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)bigpond(dot)net(dot)au>
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2002-11-13 03:37:35 | Re: Upgrade to dual processor machine? |
Previous Message | scott.marlowe | 2002-11-12 20:26:34 | Re: Upgrade to dual processor machine? |