Re: BUG #5543: Poor performance - Index scan backwards not used for order by desc with partitioned tables

On Tue, Jul 6, 2010 at 2:20 PM, Ranga Gopalan <ranga_gopalan(at)hotmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5543
> Logged by:          Ranga Gopalan
> Email address:      ranga_gopalan(at)hotmail(dot)com
> PostgreSQL version: 8.4.4
> Operating system:   Linux x86-64
> Description:        Poor performance - Index scan backwards not used for
> order by desc with partitioned tables
> Details:
>
> My problem is regarding ORDER BY / LIMIT query behavior when using
> partitioning.
>
> I have a large table (about 100 columns, several million rows) partitioned
> by a column called day (which is the date stored as yyyymmdd - say 20100502
> for May 2nd 2010 etc.). Say the main table  is called FACT_TABLE and each
> child table is called FACT_TABLE_yyyymmdd (e.g. FACT_TABLE_20100502,
> FACT_TABLE_20100503 etc.) and has an appropriate CHECK constraint created on
> it to CHECK (day = yyyymmdd).
>
> The query pattern I am looking at is (I have tried to simplify the column
> names for readability):
>
> SELECT F1 from FACT_TABLE
> where day >= 20100502 and day <= 20100507  # selecting for a week
> ORDER BY F2 desc
> LIMIT 100
>
>
> This is what is happening:
>
> When I query from the specific day's (child) table, I get what I expect - a
> descending Index scan and good performance.
>
> # explain  select F1 from FACT_TABLE_20100502 where day = 20100502 order by
> F2 desc limit 100;
>                                                                    QUERY
> PLAN
>
> ----------------------------------------------------------------------------
> --------------------------------------------------------------------
> --
>  Limit  (cost=0.00..4.81 rows=100 width=41)
>   ->  Index Scan Backward using F2_20100502 on FACT_TABLE_20100502
> (cost=0.00..90355.89 rows=1876985 width=41
> )
>         Filter: (day = 20100502)
>
>
>
> BUT:
>
> When I do the same query against the parent table it is much slower - two
> things seem to happen - one is that the descending scan of the index is not
> done and secondly there seems to be a separate sort/limit at the end - i.e.
> all data from all partitions is retrieved and then sorted and limited - This
> seems to be much less efficient than doing a descending scan on each
> partition and limiting the results and then combining and reapplying the
> limit at the end.
>
> explain  select F1 from FACT_TABLE where day = 20100502 order by F2 desc
> limit 100;
>                                                                    QUERY
> PLAN
>
> ----------------------------------------------------------------------------
> --------------------------------------------------------------------
> ---
>  Limit  (cost=20000084948.01..20000084948.01 rows=100 width=41)
>   ->  Sort  (cost=20000084948.01..20000084994.93 rows=1876986 width=41)
>         Sort Key: public.FACT_TABLE.F2
>         ->  Result  (cost=10000000000.00..20000084230.64 rows=1876986
> width=41)
>               ->  Append  (cost=10000000000.00..20000084230.64 rows=1876986
> width=41)
>                     ->  Seq Scan on FACT_TABLE
> (cost=10000000000.00..10000000010.02 rows=1 width=186)
>                           Filter: (day = 20100502)
>                     ->  Seq Scan on FACT_TABLE_20100502 FACT_TABLE
> (cost=10000000000.00..10000084220.62 rows=1876985 width=4
> 1)
>                           Filter: (day = 20100502)
> (9 rows)

Does it help if you put a CHECK (false) constraint on the parent table?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company