X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2FC229FA13E for ; Tue, 13 Jun 2006 14:53:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 06135-06 for ; Tue, 13 Jun 2006 14:53:36 -0300 (ADT) X-Greylist: delayed 00:20:31.251579 by SQLgrey- Received: from mail.leapfrogonline.com (mail.leapfrogonline.com [69.36.35.239]) by postgresql.org (Postfix) with ESMTP id EFD659F9316 for ; Tue, 13 Jun 2006 14:53:35 -0300 (ADT) Received: from leapfrogonlinecom-MTA by mail.leapfrogonline.com with Novell_GroupWise; Tue, 13 Jun 2006 12:32:53 -0500 Message-Id: <448EB053.8F27.00A9.0@leapfrogonline.com> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Beta Date: Tue, 13 Jun 2006 12:32:19 -0500 From: "Shaun Thomas" To: Subject: Confirmation of bad query plan generated by 7.4 tree Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/188 X-Sequence-Number: 19545 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