Just so I don't think I'm insane:
warehouse=# explain analyze select e.event_date::date
warehouse-# from l_event_log e
warehouse-# JOIN c_event_type t ON (t.id = e.event_type_id)
warehouse-# WHERE e.event_date > now() - interval '2 days'
warehouse-# AND t.event_name = 'activation';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=9.22..2723869.56 rows=268505 width=8) (actual
time=107.324..408.466 rows=815 loops=1)
Hash Cond: ("outer".event_type_id = "inner".id)
-> Index Scan using idx_evt_dt on l_event_log e
(cost=0.00..2641742.75 rows=15752255 width=12) (actual
time=0.034..229.641 rows=38923 loops=1)
Index Cond: (event_date > (now() - '2 days'::interval))
-> Hash (cost=9.21..9.21 rows=3 width=4) (actual time=0.392..0.392
rows=0 loops=1)
-> Index Scan using pk_c_event_type on c_event_type t
(cost=0.00..9.21 rows=3 width=4) (actual time=0.071..0.353 rows=6
loops=1)
Filter: ((event_name)::text = 'activation'::text)
Total runtime: 412.015 ms
(8 rows)
Am I correct in assuming this terrible plan is due to our ancient
version of Postgres?
This plan is so bad, the system prefers a sequence scan on our 27M row
table with dates
spanning 4 years. 2 days should come back instantly. Both tables are
freshly vacuumed
and analyzed, so I'll just chalk this up to 7.4 sucking unless someone
says otherwise.
--
Shaun Thomas
Database Administrator
Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com
Confidentiality Note:
The document(s) accompanying this e-mail transmission, if any, and the
e-mail transmittal message contain information from Leapfrog Online
Customer Acquisition, LLC is confidential or privileged. The information
is intended to be for the use of the individual(s) or entity(ies) named
on this e-mail transmission message. If you are not the intended
recipient, be aware that any disclosure, copying, distribution or use of
the contents of this e-mail is prohibited. If you have received this
e-mail in error, please immediately delete this e-mail and notify us by
telephone of the error