Hope someone can shed light on this.
I have a well-performing query that uses the index. Without OFFSET etc
it returns about 11,000 records.
I only need about 20 of these records at any given time, which is why
my LIMIT and OFFSET try to pull only the 20 records or so.
My queries are fast in general *except* the first time. The first time
I pull my 20 records, it takes quite a lot of time -- about 8-10
seconds or so, which is unacceptable in our case.
My guesses:
1. This first query slowness may be because thequery is being read
into the memory?
2. Because the query uses an ORDER BY DESC on another indexed (date)
key, which means it may well be slowing down the sorting the first
time?
Appreciate any thoughts.
My query is:
explain analyze SELECT
testimonials.url
,testimonials.alias
,testimonials.aliasEntered
,testimonials.title
,testimonials.modify_date
,testimonials.id
,visitcount.visit_count
,visitcount.unique_count
,visitcount.modify_date
,coalesce( extract(epoch from now()) - extract(epoch
from visitcount.modify_date), 0)
,(select count(id) from testimonials WHERE
testimonials.user_id = 'superman' and testimonials.user_known = 1 and
testimonials.status = 'Y' ) AS total
FROM testimonials
LEFT JOIN visitcount ON testimonials.id = visitcount.id
WHERE
testimonials.user_id = 'superman'
and testimonials.user_known = 1
and testimonials.status = 'Y'
ORDER BY testimonials.modify_date desc
OFFSET 0 LIMIT 10
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=224.68..224.71 rows=10 width=187) (actual
time=453.429..453.539 rows=10 loops=1)
InitPlan
-> Aggregate (cost=63.52..63.53 rows=1 width=8) (actual
time=89.268..89.271 rows=1 loops=1)
-> Index Scan using new_idx_userknown on testimonials
(cost=0.00..63.41 rows=42 width=8) (actual time=0.039..49.968
rows=10149 loops=1)
Index Cond: ((user_id)::text = 'superman'::text)
Filter: (status = 'Y'::bpchar)
-> Sort (cost=161.16..161.26 rows=42 width=187) (actual
time=453.420..453.464 rows=10 loops=1)
Sort Key: testimonials.modify_date
-> Nested Loop Left Join (cost=0.00..160.02 rows=42
width=187) (actual time=89.384..395.008 rows=10149 loops=1)
-> Index Scan using new_idx_userknown on testimonials
(cost=0.00..63.41 rows=42 width=171) (actual time=0.061..50.990
rows=10149 loops=1)
Index Cond: ((user_id)::text = 'superman'::text)
Filter: (status = 'Y'::bpchar)
-> Index Scan using visitcount_pkey1 on visitcount
(cost=0.00..2.28 rows=1 width=24) (actual time=0.007..0.010 rows=1
loops=10149)
Index Cond: (testimonials.id = visitcount.id)
Total runtime: 461.
682 ms
(15 rows)
It's using the following indexes on the "testimonials" table.
"new_idx_modify_date" btree (modify_date) WITH (fillfactor=75)
"new_idx_userknown" btree (user_id) WITH (fillfactor=70) WHERE user_known = 1
THANKS!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
