On Mon, Jan 26, 2009 at 2:26 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > 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. > Hi Scott. Yes, there is an autovacuum on both the tables. Should i additionally do a manual vacuum too? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
