Re: performance problem aftrer update from 7.1 to 7.4.2 - Mailing list pgsql-general

From Richard Huxton
Subject Re: performance problem aftrer update from 7.1 to 7.4.2
Date
Msg-id 200404151751.40259.dev@archonet.com
Whole thread Raw
In response to Re: performance problem aftrer update from 7.1 to 7.4.2  ("Development - multi.art.studio" <dev.null@multiartstudio.com>)
Responses Re: performance problem aftrer update from 7.1 to 7.4.2
List pgsql-general
On Thursday 15 April 2004 17:30, Development - multi.art.studio wrote:
> Hello,
>
> sorry im late, but here are more details:
>
> im wondering why 7.4 doesnt use the newsletter_site_id_date_idx-index
>
> explain with 7.1: without analyze
> mcms09=> explain select * from newsletter where site_id='m000000-970' order
> by date desc,id desc limit 10; NOTICE:  QUERY PLAN:
> Limit  (cost=9.26..9.26 rows=7 width=84)
>   ->  Sort  (cost=9.26..9.26 rows=8 width=84)
>         ->  Index Scan using newsletter_site_id_date_idx on newsletter
> (cost=0.00..9.14 rows=8 width=84) EXPLAIN
> __________________________
> and with pgsql 7.4.2:
>
> mcms=# explain analyse select * from newsletter where site_id='m000000-970'
> order by date desc,id desc limit 10;

>  Limit  (cost=17.78..17.81 rows=10 width=610) (actual time=0.625..0.645
> rows=10 loops=1)
>    ->  Sort  (cost=17.78..17.81 rows=11 width=610) (actual
> time=0.620..0.627 rows=10 loops=1)
>          Sort Key: date, id
>          ->  Index Scan using site_id_newsletter_key on newsletter
> (cost=0.00..17.59 rows=11 width=610) (actual time=0.087..0.286 rows=15
> loops=1)
>                Index Cond: ((site_id)::text = 'm000000-970'::text)
>
>  Total runtime: 0.766 ms

It's not using the date index because it's using the id index - there's only
10 matches, so that looks like a good choice to me. It takes less than 1ms,
so I'm not sure this is a good example of a problem.

--
  Richard Huxton
  Archonet Ltd

pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: Problems with Triggers
Next
From: "Development - multi.art.studio"
Date:
Subject: Re: Filesystem vs. Postgres for images