Re: Slow first query despite LIMIT and OFFSET clause

On Jan 28, 2009, at 1:38 PM, Phoenix Kiula wrote:

> Thanks Alban. We have now made all the triggers and such. That part is
> working. I suppose not having the count(id) is helping just with a few
> seconds, but the query is still taking about 15 seconds in some cases.
>
> Here are the query and its exec plan again fyi. Any other ideas for
> tweaking?

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.

A few things in this query appear to take relatively much time:

- The index scans on new_idx_userknown; What's worrying there is that
the planner expects only a few rows (42) while in actuality they are
quite many (10149). This scan is performed twice too! It seems that
the statistics that index uses are off. That may mean changing the
statistics on the columns involved or increasing the frequency that
autovacuum visits them.

- The nested loop left join is expensive; That's probably also due to
the incorrect assumptions the planner makes about the index scans I
mentioned above. It expects to have to loop 42 times, but ends up
doing so 10149 times instead! I believe loops aren't particularly
efficient, they'll only beat other methods if there are few rows to
loop through.
The loop is taking 395-89 = 306 ms for 10149 rows, while the planner
expected it to take 306 * (42/10149) = 1.3 ms. Quite a difference!

You probably need to do something about new_idx_userknown. A partial
index (as suggested elsewhere) may help make it smaller (easier to fit
in RAM, fewer branches required to find a node), but the bad
statistics are likely to be the real problem here. Without knowing
anything about that particular index and the tables it's indexing it's
hard to tell how to improve it.

> 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)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4980a309747032541118883!