On Sun, Jan 25, 2009 at 8:41 PM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: > 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) Have you analyzed these tables? The estimates and real row counts are quite different. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
