Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4DC2E64FD64 for ; Sun, 21 Dec 2008 16:08:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54864-04 for ; Sun, 21 Dec 2008 16:08:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id EC0A864FCDE for ; Sun, 21 Dec 2008 16:08:42 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Sun, 21 Dec 2008 12:08:40 -0800 From: Scott Carey To: "pgsql-performance@postgresql.org" Date: Sun, 21 Dec 2008 12:08:36 -0800 Subject: Query planner plus partitions equals very bad plans, again Thread-Topic: Query planner plus partitions equals very bad plans, again Thread-Index: Acljp+gDzCz6wvEWwUSrl1jQGcVUfA== Message-ID: Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C573E2441577scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.411 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200812/178 X-Sequence-Number: 32214 --_000_C573E2441577scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Here is a query on a partitioned schema that produces a very bad query plan= . The tables are fully vacuumed, analyzed with stats target 40, and no blo= at (created with pure inserts, no updates or deletes). I already know of at least three bugs with the query planner and partitions= listed at the end of this message. Below are explain analyze examples on just one partition. Expanding the da= te range to be multiple partitions only makes this worse. First, the query written to go against the direct partitions, which is fast= and does the right plan: (although, wouldn't it use less memory if it hashed the small table, and sc= anned the larger one against that rather than vice-versa? Or am I reading = the explain wrong?) rr=3D> explain analyze select pl.id, sl.terms from p_pp_logs_026_2008_12_17= pl, p_s_logs_026_2008_12_17 sl where sl.session =3D pl.session and sl.te= rms <> 'null' and sl.terms is not null; = QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------------- Hash Join (cost=3D31470.89..37199.26 rows=3D38597 width=3D27) (actual tim= e=3D743.782..772.705 rows=3D29907 loops=3D1) Hash Cond: ((pl.session)::text =3D (sl.session)::text) -> Seq Scan on pp_logs_026_2008_12_17 pl (cost=3D0.00..357.49 rows=3D8= 449 width=3D46) (actual time=3D0.007..3.064 rows=3D8449 loops=3D1) -> Hash (cost=3D24822.35..24822.35 rows=3D531883 width=3D57) (actual t= ime=3D743.597..743.597 rows=3D531453 loops=3D1) -> Seq Scan on s_logs_026_2008_12_17 sl (cost=3D0.00..24822.35 r= ows=3D531883 width=3D57) (actual time=3D0.011..392.242 rows=3D532067 loops= =3D1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'null'::t= ext)) Total runtime: 779.431 ms In the above, the estimated and actual rows are about right, only a bit off= after the join. We are partitioned by s_id and date, the general form of the query used for= scanning more than one combination of these values fails. In the below, which restricts the data to the same tables, the planner is e= xtremely wrong (plus the parent tables which have one irrelevant row of dum= my data so that analyze works on them): explain analyze select pl.id, sl.terms from pp_logs pl, s_logs sl where pl= .s_id =3D 26 and sl.s_id =3D 26 and sl.session =3D pl.session and sl.terms= <> 'null' and sl.terms is not null and pl.date =3D '2008-12-17' and sl.da= te =3D '2008-12-17'; Q= UERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------- Merge Join (cost=3D79036.43..416160.16 rows=3D22472099 width=3D27) (actua= l time=3D7089.961..7839.665 rows=3D29907 loops=3D1) Merge Cond: ((pl.session)::text =3D (sl.session)::text) -> Sort (cost=3D968.98..990.10 rows=3D8450 width=3D46) (actual time=3D= 193.778..196.690 rows=3D8449 loops=3D1) Sort Key: pl.session Sort Method: quicksort Memory: 1043kB -> Append (cost=3D0.00..417.84 rows=3D8450 width=3D46) (actual t= ime=3D36.824..133.157 rows=3D8449 loops=3D1) -> Seq Scan on pp_logs pl (cost=3D0.00..18.10 rows=3D1 wid= th=3D21) (actual time=3D0.056..0.056 rows=3D0 loops=3D1) Filter: ((s_id =3D 26) AND (date =3D '2008-12-17'::dat= e)) -> Seq Scan on pp_logs_026_2008_12_17 pl (cost=3D0.00..399= .74 rows=3D8449 width=3D46) (actual time=3D36.766..129.317 rows=3D8449 loop= s=3D1) Filter: ((s_id =3D 26) AND (date =3D '2008-12-17'::dat= e)) -> Sort (cost=3D78067.45..79397.16 rows=3D531884 width=3D57) (actual t= ime=3D6895.648..7094.701 rows=3D552412 loops=3D1) Sort Key: sl.session Sort Method: quicksort Memory: 92276kB -> Append (cost=3D0.00..27483.28 rows=3D531884 width=3D57) (actu= al time=3D33.759..1146.936 rows=3D532067 loops=3D1) -> Seq Scan on s_logs sl (cost=3D0.00..1.19 rows=3D1 width= =3D22) (actual time=3D0.036..0.036 rows=3D0 loops=3D1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'nu= ll'::text) AND (s_id =3D 26) AND (date =3D '2008-12-17'::date)) -> Seq Scan on s_logs_026_2008_12_17 sl (cost=3D0.00..2748= 2.09 rows=3D531883 width=3D57) (actual time=3D33.721..903.607 rows=3D532067= loops=3D1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'nu= ll'::text) AND (s_id =3D 26) AND (date =3D '2008-12-17'::date)) Total runtime: 7861.723 ms Some partitions have about 10x the data as the above, resulting in ~30x lon= ger times to do the sort, but only 10x to hash. The sort also uses far, fa= r more memory than the hash should if it were to hash the small one and has= h-join the large table against that. As you can see in the above example, this inner join, which the planner kno= ws has two arms of row count ~530K and ~8.5K, is predicted to have 22M outp= ut rows. What? Did it loose its ability to even roughly know n_distinct? Can it use n_dis= tinct for each table and combine them for a (weighted) worst case estimate = and arrive at something less bizarre? If I scan across more tables, the actual output grows roughly linearly, but= the estimate looks like its A*ln(A*B) where A is the size of the larger ar= m and B is the size of the smaller one. Having an inner join result in more= rows than one of the arms is difficult with n_distinct numbers like these = as evidenced by the direct to table query estimate. Perhaps the parent table (with one row) is getting in the way? Any suggestions on how to get a good plan when accessing this via the paren= t table to scan multiple dates? Known (to me) partition bugs related to the query planner / stats: * Row width estimates for scans across multiple tables are wrong (just max(= each table width) rather than a weighted average - this could have been fix= ed in 8.3.4+ without me noticing though). * Column selectivity not combined rationally for aggregate statistics on mu= ltiple table scans in an arm. Example, two partitions may have an int colu= mn that has only 30 distinct values over 2M rows in each partition. The pl= anner will decide that the selectivity is 10% rather than the worst case (3= 0+30)/(total rows) or best case (30/(total rows)). There are many variati= ons on this theme not just the above type of error. * An empty partition (usually the parent table) will propagate default stat= istics through the errors above leading to even more strange query plans. = Empty table default statistics are generally poor, but worse in the cases a= bove since they override good stats. Furthermore, ANALYZE won't write stat= s for an empty table so unless you put dummy rows in a table, you are guara= nteed to get bad plans. I have put a single dummy row in each parent table with minimum column widt= h on text columns and analyzed it to help out on the above. All partitions= are fully vacuumed with no bloat and fully analyzed. After a day, they be= come read only. The only time I wish I have hints is when I'm working on partitioned tables= , most other times minor query modifications plus good statistics get a goo= d plan (but perhaps a less natural query syntax). The planner is not adequ= ate on partitioned tables. --_000_C573E2441577scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Query planner plus partitions equals very bad plans, again Here is a query on a partitioned schema that produces a very bad quer= y plan.  The tables are fully vacuumed, analyzed with stats target 40,= and no bloat (created with pure inserts, no updates or deletes).

I already know of at least three bugs with the query planner and partitions= listed at the end of this message.

Below are explain analyze examples on just one partition.  Expanding t= he date range to be multiple partitions only makes this worse.  

First, the query written to go against the direct partitions, which is fast= and does the right plan:
(although, wouldn’t it use less memory if it hashed the small table, = and scanned the larger one against that rather than vice-versa?  Or am= I reading the explain wrong?)

rr=3D> explain analyze select pl.id, sl.terms from p_pp_logs_026_2008_12= _17 pl, p_s_logs_026_2008_12_17 sl  where sl.session =3D pl.session an= d  sl.terms <> 'null' and sl.terms is not null;   &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;  QUERY PLAN         = ;            &n= bsp;            = ;            &n= bsp;            = ;          
---------------------------------------------------------------------------= -------------------------------------------------------------------------  Hash Join  (cost=3D31470.89..37199.26 rows=3D38597 width=3D27) (= actual time=3D743.782..772.705 rows=3D29907 loops=3D1)
   Hash Cond: ((pl.session)::text =3D (sl.session)::text)    ->  Seq Scan on pp_logs_026_2008_12_17 pl  (= cost=3D0.00..357.49 rows=3D8449 width=3D46) (actual time=3D0.007..3.064 row= s=3D8449 loops=3D1)
   ->  Hash  (cost=3D24822.35..24822.35 rows=3D= 531883 width=3D57) (actual time=3D743.597..743.597 rows=3D531453 loops=3D1)=
         ->  Seq Scan = on s_logs_026_2008_12_17 sl  (cost=3D0.00..24822.35 rows=3D531883 widt= h=3D57) (actual time=3D0.011..392.242 rows=3D532067 loops=3D1)
            &nb= sp;  Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'nu= ll'::text))
 Total runtime: 779.431 ms

In the above, the estimated and actual rows are about right, only a bit off= after the join.

We are partitioned by s_id and date, the general form of the query used for= scanning more than one combination of these values fails.
In the below, which restricts the data to the same tables, the planner is e= xtremely wrong (plus the parent tables which have one irrelevant row of dum= my data so that analyze works on them):

explain analyze select pl.id, sl.terms from pp_logs pl, s_logs sl  whe= re pl.s_id =3D 26 and sl.s_id =3D 26 and sl.session =3D pl.session and &nbs= p;sl.terms <> 'null' and sl.terms is not null  and pl.date =3D '= 2008-12-17' and sl.date =3D '2008-12-17';
            &nb= sp;            =             &nb= sp;            =             &nb= sp;           QUERY = PLAN            &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            <= BR> ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------
 Merge Join  (cost=3D79036.43..416160.16 rows=3D22472099 width=3D= 27) (actual time=3D7089.961..7839.665 rows=3D29907 loops=3D1)
   Merge Cond: ((pl.session)::text =3D (sl.session)::text)    ->  Sort  (cost=3D968.98..990.10 rows=3D8450= width=3D46) (actual time=3D193.778..196.690 rows=3D8449 loops=3D1)
         Sort Key: pl.session<= BR>          Sort Method:  qu= icksort  Memory: 1043kB
         ->  Append &n= bsp;(cost=3D0.00..417.84 rows=3D8450 width=3D46) (actual time=3D36.824..133= .157 rows=3D8449 loops=3D1)
            &nb= sp;  ->  Seq Scan on pp_logs pl  (cost=3D0.00..18.10= rows=3D1 width=3D21) (actual time=3D0.056..0.056 rows=3D0 loops=3D1)
            &nb= sp;        Filter: ((s_id =3D 26) A= ND (date =3D '2008-12-17'::date))
            &nb= sp;  ->  Seq Scan on pp_logs_026_2008_12_17 pl  (cos= t=3D0.00..399.74 rows=3D8449 width=3D46) (actual time=3D36.766..129.317 row= s=3D8449 loops=3D1)
            &nb= sp;        Filter: ((s_id =3D 26) A= ND (date =3D '2008-12-17'::date))
   ->  Sort  (cost=3D78067.45..79397.16 rows=3D= 531884 width=3D57) (actual time=3D6895.648..7094.701 rows=3D552412 loops=3D= 1)
         Sort Key: sl.session<= BR>          Sort Method:  qu= icksort  Memory: 92276kB
         ->  Append &n= bsp;(cost=3D0.00..27483.28 rows=3D531884 width=3D57) (actual time=3D33.759.= .1146.936 rows=3D532067 loops=3D1)
            &nb= sp;  ->  Seq Scan on s_logs sl  (cost=3D0.00..1.19 r= ows=3D1 width=3D22) (actual time=3D0.036..0.036 rows=3D0 loops=3D1)
            &nb= sp;        Filter: ((terms IS NOT N= ULL) AND ((terms)::text <> 'null'::text) AND (s_id =3D 26) AND (date = =3D '2008-12-17'::date))
            &nb= sp;  ->  Seq Scan on s_logs_026_2008_12_17 sl  (cost= =3D0.00..27482.09 rows=3D531883 width=3D57) (actual time=3D33.721..903.607 = rows=3D532067 loops=3D1)
            &nb= sp;        Filter: ((terms IS NOT N= ULL) AND ((terms)::text <> 'null'::text) AND (s_id =3D 26) AND (date = =3D '2008-12-17'::date))
 Total runtime: 7861.723 ms

Some partitions have about 10x the data as the above, resulting in ~30x lon= ger times to do the sort, but only 10x to hash.  The sort also uses fa= r, far more memory than the hash should if it were to hash the small one an= d hash-join the large table against that.

As you can see in the above example, this inner join, which the planner kno= ws has two arms of row count ~530K and ~8.5K, is predicted to have 22M outp= ut rows.  What?  
Did it loose its ability to even roughly know n_distinct?  Can it use = n_distinct for each table and combine them for a (weighted) worst case esti= mate and arrive at something less bizarre?
If I scan across more tables, the actual output grows roughly linearly, but= the estimate looks like its A*ln(A*B) where A is the size of the larger ar= m and B is the size of the smaller one. Having an inner join result in more= rows than one of the arms is difficult with n_distinct numbers like these = as evidenced by the direct to table query estimate.
Perhaps the parent table (with one row) is getting in the way?

Any suggestions on how to get a good plan when accessing this via the paren= t table to scan multiple dates?
 

Known (to me) partition bugs related to the query planner / stats:   * Row width estimates for scans across multiple tables are wrong (just max(= each table width) rather than a weighted average — this could have be= en fixed in 8.3.4+ without me noticing though).
* Column selectivity not combined rationally for aggregate statistics on mu= ltiple table scans in an arm.  Example, two partitions may have an int= column that has only 30 distinct values over 2M rows in each partition. &n= bsp;The planner will decide that the selectivity is 10% rather than the wor= st case (30+30)/(total rows) or best case (30/(total rows)).   Th= ere are many variations on this theme not just the above type of error.
* An empty partition (usually the parent table) will propagate default stat= istics through the errors above leading to even more strange query plans. &= nbsp;Empty table default statistics are generally poor, but worse in the ca= ses above since they override good stats.  Furthermore, ANALYZE won= 217;t write stats for an empty table so unless you put dummy rows in a tabl= e, you are guaranteed to get bad plans.

I have put a single dummy row in each parent table with minimum column widt= h on text columns and analyzed it to help out on the above.  All parti= tions are fully vacuumed with no bloat and fully analyzed.  After a da= y, they become read only.

The only time I wish I have hints is when I’m working on partitioned = tables, most other times minor query modifications plus good statistics get= a good plan (but perhaps a less natural query syntax).  The planner i= s not adequate on partitioned tables. --_000_C573E2441577scottrichrelevancecom_--