Re: performance problem aftrer update from 7.1 to 7.4.2

["Development - multi.art.studio" <dev.null@multiartstudio.com>]

hello everyone,

Richard Huxton wrote:

>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.

thanks all !
but this doesnt help me a lot, i dont know what what to do? im not an expert. 
ok, i could use another site_id  and it will grow up and gives 500 or more rows back from the select clause.
this takes a lot of more time. 
would this help?

for me it looks as pg742 is not as performant as 7.1 ? and takes a lot of more time, and doesnt look for the correct index? but why this?
why does it take longer than in 7.1? any ideas? what can i do? 

any help would be greatly apreciated.

yours volker
sory for the bad english

Richard Huxton wrote:

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.