speeding up a query

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: Raw Message | Whole Thread | 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)

Responses

Browse pgsql-general by date

  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?