Re: Slow first query despite LIMIT and OFFSET clause

On Thu, Jan 29, 2009 at 2:25 AM, Alban Hertroys
<dalroi@solfertje.student.utwente.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!