Re: Slow first query despite LIMIT and OFFSET clause

On Thu, Jan 29, 2009 at 2:25 AM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> Ah I see, that's the original query and its plan again, not the one after
> implementing those triggers! You had me scratching my head for a bit there,
> wondering why the count() subquery was still there.

Yes that was just for info. Here are the new query without the count()
in there:

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)
FROM testimonials
LEFT OUTER JOIN visitcount USING (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=61.42..61.44 rows=10 width=162) (actual
time=105.400..105.499 rows=10 loops=1)
-> Sort (cost=61.42..61.46 rows=16 width=162) (actual
time=105.392..105.425 rows=10 loops=1)
Sort Key: testimonials.modify_date
-> Nested Loop Left Join (cost=0.00..61.10 rows=16
width=162) (actual time=0.092..94.516 rows=2027 loops=1)
-> Index Scan using new_idx_userknown on testimonials
(cost=0.00..24.29 rows=16 width=146) (actual time=0.058..10.983
rows=2027 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.024..0.026 rows=1
loops=2027)
Index Cond: (testimonials.id = visitcount.id)
Total runtime: 105.652 ms
(10 rows)

Note that I have an index on user_id, but because this is a website,
there are several user_ids where we only have the IP. The above query
is only ever needed for registered users, so for just the registered
users we created another partial index called

"new_idx_userknown" btree (user_id) WHERE user_known = 1

Of course for unregistered users we use user_known = 0, so they are
excluded from this index. Is this not a useful partial index? I think
in this SQL, the user_id is always "superman" and the user_known
always 1 which is why the guesstimate from the planner may be off?

Love to hear thoughts.

THANKS!