| From: | Marcus Engene <mengpg2(at)engene(dot)se> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | speeding up a query | 
| Date: | 2007-04-03 20:12:44 | 
| Message-ID: | 4612B53C.30106@engene.se | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi,
I'm on 8.0.10 and there is a query I cannot quite get adequately fast.
Should it take 2.5s to sort these 442 rows? Are my settings bad? Is
my query stupid?
Would appreciate any tips.
Best regards,
Marcus
apa=> explain analyze
apa->  select
apa->      ai.objectid as ai_objectid
apa->  from
apa->      apa_item ai
apa->  where
apa->      idxfti @@ to_tsquery('default', 'KCA0304')  AND
apa->      ai.status = 30
apa->  ORDER BY ai.calc_rating desc
apa->  LIMIT 1000;
 Limit  (cost=54.40..54.43 rows=12 width=8) (actual 
time=2650.254..2651.093 rows=442 loops=1)
   ->  Sort  (cost=54.40..54.43 rows=12 width=8) (actual 
time=2650.251..2650.515 rows=442 loops=1)
         Sort Key: calc_rating
         ->  Index Scan using apa_item_fts on apa_item ai  
(cost=0.00..54.18 rows=12 width=8) (actual time=61.261..2649.045 
rows=442 loops=1)
               Index Cond: (idxfti @@ '''kca0304'''::tsquery)
               Filter: (status = 30)
 Total runtime: 2651.659 ms
(7 rows)
apa=> explain analyze
apa->  select
apa->      ai.objectid as ai_objectid
apa->  from
apa->      apa_item ai
apa->  where
apa->      idxfti @@ to_tsquery('default', 'KCA0304')  AND
apa->      ai.status = 30
apa->  LIMIT 1000;
 Limit  (cost=0.00..54.18 rows=12 width=4) (actual time=0.186..18.628 
rows=442 loops=1)
   ->  Index Scan using apa_item_fts on apa_item ai  (cost=0.00..54.18 
rows=12 width=4) (actual time=0.183..17.999 rows=442 loops=1)
         Index Cond: (idxfti @@ '''kca0304'''::tsquery)
         Filter: (status = 30)
 Total runtime: 19.062 ms
(5 rows)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Broersma Jr | 2007-04-03 21:17:01 | assistance needed for autovacuum on the windows version of 8.2.3 | 
| Previous Message | Jaime Silvela | 2007-04-03 19:18:34 | Re: COPY FROM - how to identify results? |