First one, try to paste explain into http://explain.depesz.com/ > select > a.id, > ident_id, > time, > customer_name, > extract('day' from timezone(e.name, to_timestamp(a.time))) as day, > category_id > from > pwreport.url_hits a left outer join > pwreport.url_hits_category_jt c on (a.id = c.url_hits_id), url_hits_category_jt has an index on url_hits_id? > pwreport.ident b, > pwreport.timezone e > where > a.ident_id = b.id > and b.timezone_id = e.id > and time >= extract ('epoch' from timestamp '2009-08-12') > and time < extract ('epoch' from timestamp '2009-08-13' ) > and direction = 'REQUEST' > ; > QUERY > PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Merge Right Join (cost=47528508.61..180424544.59 rows=10409251 width=53) > Merge Cond: (c.url_hits_id = a.id) > -> Index Scan using mt_url_hits_category_jt_url_hits_id_index on > url_hits_category_jt c (cost=0.00..122162596.63 rows=4189283233 width=8) > -> Sort (cost=47528508.61..47536931.63 rows=3369210 width=49) > Sort Key: a.id > -> Hash Join (cost=2565.00..47163219.21 rows=3369210 width=49) > Hash Cond: (b.timezone_id = e.id) > -> Hash Join (cost=2553.49..47116881.07 rows=3369210 > width=37) > Hash Cond: (a.ident_id = b.id) > -> Seq Scan on url_hits a (cost=0.00..47051154.89 > rows=3369210 width=12) for some reason (think absence of index) this part of the query is heavy. Do you run analyze to your database? Vacuum and reindex? > Filter: ((direction = > 'REQUEST'::proxy_direction_enum) AND (("time")::double precision >= > 1250035200::double precision) AND (("time")::double precision < > 1250121600::double precision)) > -> Hash (cost=2020.44..2020.44 rows=42644 width=29) > -> Seq Scan on ident b (cost=0.00..2020.44 > rows=42644 width=29) > -> Hash (cost=6.78..6.78 rows=378 width=20) > -> Seq Scan on timezone e (cost=0.00..6.78 rows=378 > width=20) > (15 rows) > > > -- -- Emanuel Calvo Franco Database consultant at: www.siu.edu.ar www.emanuelcalvofranco.com.ar -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
