X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5F095D1B508 for ; Thu, 13 Nov 2003 18:47:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04220-10 for ; Thu, 13 Nov 2003 14:46:52 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 5E127D1B554 for ; Thu, 13 Nov 2003 14:46:51 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hADIkp19005807; Thu, 13 Nov 2003 13:46:51 -0500 (EST) To: "Arthur Ward" Cc: pgsql-performance@postgresql.org Subject: Re: Union+group by planner estimates way off? In-reply-to: <23123.68.62.129.152.1068748095.squirrel@award.gotdns.org> References: <23123.68.62.129.152.1068748095.squirrel@award.gotdns.org> Comments: In-reply-to "Arthur Ward" message dated "Thu, 13 Nov 2003 12:28:15 -0600" Date: Thu, 13 Nov 2003 13:46:51 -0500 Message-ID: <5806.1068749211@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/95 X-Sequence-Number: 4655 "Arthur Ward" writes: > EXPLAIN ANALYZE SELECT id FROM > (SELECT id, commodity FROM commodities WHERE commodity IS NOT NULL > UNION > SELECT id, fak FROM commodities WHERE fak IS NOT NULL > ) all_commodities GROUP BY id; > QUERY > PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=15939.16..15939.16 rows=200 width=4) (actual > time=3537.281..3680.418 rows=83306 loops=1) > -> Subquery Scan all_commodities (cost=14002.00..15697.02 rows=96858 > width=4) (actual time=2268.052..3214.996 rows=95715 loops=1) It's falling back to a default estimate because it doesn't know how to find any statistics for the output of a sub-select. I have a TODO somewhere about burrowing down into sub-selects to see if the output maps directly to a column that we'd have stats for ... but it's not done yet. In this particular case the inaccurate estimate doesn't matter too much, I think, although it might be encouraging the system to select hash aggregation since it thinks the hashtable will be pretty small. If the estimate were getting used to plan higher-up plan steps then it could be a bigger problem. regards, tom lane