Re: performance problem aftrer update from 7.1 to 7.4.2

On Thu, 22 Apr 2004, Development - multi.art.studio wrote:

> i did some mistakes, site-id was wrong in my simple select querys.
> (interesting that no matches would take double time on 7.4)
>
> here are the 'right' results, showing to me postgres 7.4 is slightly
> slower with simple querys (but 1000-times faster with more complex
> querys ;-)
>
> with production 7.1:
>
> mcms09=> select count(id) from newsletter where aktiv=1 and
> site_id='m200384-000';
> count
> -------
> 845
> (1 row)
>
> ---some users where working on it and added two newsletter since last
> dump....
>
> mcms09=> explain select * from newsletter where aktiv=1 and
> site_id='m200384-000' order by id desc,date desc;
> NOTICE: QUERY PLAN:
> Sort (cost=123.78..123.78 rows=841 width=84)
> -> Seq Scan on newsletter (cost=0.00..82.95 rows=841 width=84)
> EXPLAIN
>
>
> and 7.4 test:
>
> mcms=# select count(id) from newsletter where aktiv=1 and
> site_id='m200384-000';
> count
> -------
> 843
> (1 row)
>
> mcms=# explain select * from newsletter where aktiv=1 and
> site_id='m200384-000' order by id desc,date desc;
> QUERY PLAN
> ---------------------------------------------------------------------------
> Sort (cost=124.81..126.91 rows=841 width=598)
> Sort Key: id, date
> -> Seq Scan on newsletter (cost=0.00..83.95 rows=841 width=598)
> Filter: ((aktiv = 1) AND ((site_id)::text = 'm200384-000'::text))
> (4 rows)
>
>
> so in 7.4 seq-scan is about '1' (ms or what?) slower than 7.1

The cost numbers from explain have no direct connection to real time.
You'd actually have to compare the time it took to get the results from
the two in order to see how they ran (and be very careful about caching
effects and the like).