Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 224D21337BD6 for ; Thu, 12 May 2011 12:49:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 41254-02 for ; Thu, 12 May 2011 15:48:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.peak6.com (smtp.optionshouse.com [38.98.137.6]) by mail.postgresql.org (Postfix) with ESMTP id B87031337BD2 for ; Thu, 12 May 2011 12:48:57 -0300 (ADT) X-AuditID: 0a64cb1e-b7c43ae000003d6d-6d-4dcc0168cb53 Message-ID: <4DCC0167.8080903@peak6.com> Date: Thu, 12 May 2011 10:48:55 -0500 From: Shaun Thomas Reply-To: Organization: Peak6 User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1 MIME-Version: 1.0 To: Tom Lane CC: Josh Berkus , Subject: Re: Postgres refusing to use >1 core References: <4DC81A3F020000250003D433@gw.wicourts.gov> <4DC81D56020000250003D438@gw.wicourts.gov> <4DC8B893.3060908@postnewspapers.com.au> <4DC9E7C5.9080309@postnewspapers.com.au> <4DCAB3A1.2090507@peak6.com> <201105111953.060147@ms14.lnh.mail.rcn.net> <4DCB0802.9060505@peak6.com> <4DCB346E.8070608@agliodbs.com> <15686.1305211902@sss.pgh.pa.us> In-Reply-To: <15686.1305211902@sss.pgh.pa.us> Content-Type: text/plain; charset="ISO-8859-1" content-transfer-encoding: 7bit X-Originating-IP: [10.10.221.50] X-Brightmail-Tracker: AAAAAQAAAZE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201105/178 X-Sequence-Number: 43687 On 05/12/2011 09:51 AM, Tom Lane wrote: > It does. I was a bit surprised that Shaun apparently got a plan that > didn't include a materialize step, because when I test a similar query > here, I get: Remember when I said "old version" that prevented us from using CTEs? We're still on 8.2 (basically, we're waiting for EnterpriseDB 9.0). It's basically calculating the group aggregation wrong, but is that enough to trigger it to go nuts? SELECT c.* FROM customer c JOIN (SELECT session_id, count(1) as counter FROM session WHERE date_created >= '2011-05-11 05:00' AND date_created < '2011-05-11 06:00' AND from_interface = 'UNKNOWN' GROUP BY 1) a ON (c.customer_id = a.session_id) WHERE c.process_date = CURRENT_DATE - interval '1 day' AND c.row_out IS NULL; So sayeth the planner: Nested Loop (cost=167.49..2354.62 rows=6 width=237) (actual time=43.949..166858.604 rows=168 loops=1) -> GroupAggregate (cost=167.49..176.97 rows=2 width=8) (actual time=1.042..2.827 rows=209 loops=1) -> Sort (cost=167.49..170.64 rows=1260 width=8) (actual time=1.037..1.347 rows=230 loops=1) Sort Key: session.session_id -> Index Scan using idx_session_date_created on session (cost=0.00..102.61 rows=1260 width=8) (actual time=0.044. .0.690 rows=230 loops=1) Index Cond: ((date_created >= '11-MAY-11 05:00:00'::timestamp without time zone) AND (date_created < '11-MAY-11 06:00:00':: timestamp without time zone)) Filter: ((from_interface)::text = 'UNKNOWN'::text) -> Index Scan using idx_customer_customer_id on customer c (cost=0.00..1088.78 rows=3 width=237) (actual time=19.820..798.348 rows=1 loops= 209) Index Cond: (c.customer_id = a.session_id) Filter: ((process_date = (('now'::text)::date - '@ 1 day'::interval)) AND (row_out IS NULL)) Total runtime: 166859.040 ms That one hour extract is much, much slower than this: SELECT 1 FROM customer c JOIN (SELECT session_id, count(*) as counter FROM session WHERE date_created >= '2011-05-08' GROUP BY 1) a ON (c.customer_id = a.session_id) WHERE c.process_date = CURRENT_DATE AND c.row_out IS NULL; Which gives this plan: Merge Join (cost=244565.52..246488.78 rows=377 width=0) (actual time=1958.781..2385.667 rows=22205 loops=1) Merge Cond: (a.session_id = c.customer_id) -> GroupAggregate (cost=19176.22..20275.99 rows=271 width=8) (actual time=1142.179..1459.779 rows=26643 loops=1) -> Sort (cost=19176.22..19541.68 rows=146184 width=8) (actual time=1142.152..1374.328 rows=179006 loops=1) Sort Key: session.session_id -> Index Scan using idx_session_date_created on session (cost=0.00..6635.51 rows=146184 width=8) (actual time=0.0 20..160.339 rows=179267 loops=1) Index Cond: (date_created >= '08-MAY-11 00:00:00'::timestamp without time zone) -> Sort (cost=225389.30..225797.47 rows=163267 width=8) (actual time=816.585..855.459 rows=155067 loops=1) Sort Key: c.customer_id -> Index Scan using idx_customer_rpt on customer c (cost=0.00..211252.93 rows=163267 width=8) (actual time=0.037..90.337 rows=155067 loops=1) Index Cond: (process_date = '10-MAY-11 00:00:00'::timestamp without time zone) Filter: (row_out IS NULL) But make the inner query slightly smaller, and... Nested Loop (cost=13755.53..223453.98 rows=276 width=0) -> GroupAggregate (cost=13755.53..14558.26 rows=198 width=8) -> Sort (cost=13755.53..14022.28 rows=106700 width=8) Sort Key: session.session_id -> Index Scan using idx_session_date_created on session (cost=0.00..4844.37 rows=106700 width=8) Index Cond: (date_created >= '09-MAY-11 00:00:00'::timestamp without time zone) -> Index Scan using idx_customer_customer_id on customer c (cost=0.00..1055.01 rows=1 width=8) Index Cond: (c.customer_id = a.session_id) Filter: ((process_date = '10-MAY-11 00:00:00'::timestamp without time zone) AND (row_out IS NULL)) I didn't want to wait two hours for that to finish. ;) But the stats are all pretty darn close, so far as I can tell. The only thing that's off is the group aggregate... by about two orders of magnitude. So I just chalked it up to 8.2 being relatively horrible, and punted to just using a temp table to trick the optimizer into doing it right. But my greater point was that even doing it all in SQL doesn't always work, which we all know. Use of EXPLAIN abounds, but that doesn't necessarily mean a dev will know how to fix a bad plan. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@peak6.com ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email