pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arthur Ward <award@dominionsciences.com>
To: pgsql-performance@postgresql.org
Subject: Union+group by planner estimates way off?
Date: Thu, 13 Nov 2003 12:28:15 -0600 (CST)
Message-ID: <23123.68.62.129.152.1068748095.squirrel@award.gotdns.org> (raw)

On 7.4 RC2, I'm seeing a case where the query planner estimates are way
out of line after grouping the result of a union. I've tried adjusting the
statistics targets up to 200, and it made no difference in the planner's
estimates. The point of the full query this came from is that it also has
an aggregate function that produces a space-delimited list of commodity &
fak for each id. Does anyone have any suggestions on tweaks to apply or
ways to rewrite this? Is this one of those ugly corners where the query
planner doesn't have a clue how to estimate this (seeing the nice round
200 estimate makes me suspicious)?

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)
         ->  Unique  (cost=14002.00..14728.44 rows=96858 width=15) (actual
time=2268.043..2881.688 rows=95715 loops=1)
               ->  Sort  (cost=14002.00..14244.15 rows=96858 width=15)
(actual time=2268.037..2527.083 rows=100008 loops=1)
                     Sort Key: id, commodity
                     ->  Append  (cost=0.00..5034.42 rows=96858 width=15)
(actual time=7.402..1220.320 rows=100008 loops=1)
                           ->  Subquery Scan "*SELECT* 1" 
(cost=0.00..2401.23 rows=36831 width=15)
(actual time=7.398..590.004 rows=39772 loops=1)
                                 ->  Seq Scan on commodities 
(cost=0.00..2032.92 rows=36831 width=15)
(actual time=7.388..468.415 rows=39772
loops=1)
                                       Filter: (commodity IS NOT NULL)
                           ->  Subquery Scan "*SELECT* 2" 
(cost=0.00..2633.19 rows=60027 width=14)
(actual time=0.016..408.160 rows=60236 loops=1)
                                 ->  Seq Scan on commodities 
(cost=0.00..2032.92 rows=60027 width=14)
(actual time=0.010..221.635 rows=60236
loops=1)
                                       Filter: (fak IS NOT NULL)
 Total runtime: 3783.009 ms
(13 rows)




view thread (3+ messages)  latest in thread

Message-ID: <23123.68.62.129.152.1068748095.squirrel@award.gotdns.org>
Permalink:  ../23123.68.62.129.152.1068748095.squirrel@award.gotdns.org/
Also on:    postgresql.org/message-id/23123.68.62.129.152.1068748095.squirrel@award.gotdns.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: award@dominionsciences.com
  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>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox