Re: Partitioned Tables and ORDER BY

On 10/19/09 12:10 PM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:

> 2009/10/19 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>>
>>
>> 2009/10/19 Robert Haas <robertmhaas(at)gmail(dot)com>
>>>
>>> 2009/10/19 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>>>>
>>>>
>>>> On Sun, Oct 11, 2009 at 3:30 PM, Michal Szymanski <mich20061(at)gmail(dot)com>
>>>> wrote:
>>>>>
>>>>> We have similar problem and now we are try to find solution. When you
>>>>> execute query on partion there is no sorting - DB use index to
>>>>> retrieve data and if you need let say 50 rows it reads 50 rows using
>>>>> index. But when you execute on parent table query optymizer do this:
>>>>>
>>>>>  ->  Sort  (cost=726844.88..748207.02 rows=8544855 width=37739)
>>>>> (actual time=149864.868..149864.876 rows=50 loops=1)
>>>>>
>>>>> it means 8544855 rows should be sorted and it takes long minutes.
>>>>
>>>> The figures in first parenthesis are estimates, not the actual row
>>>> count.
>>>> If you think it is too low, increase statistic target for that column.
>>>
>>> It's true that the figures in parentheses are estimates, it's usually
>>> bad when the estimated and actual row counts are different by 5 orders
>>> of magnitude, and that large of a difference is not usually fixed by
>>> increasing the statistics target.
>>>
>> I thought that this means, that either analyze was running quite a long time
>> ago, or that the value didn't made it to histogram. In the later case,
>> that's mostly case when your statistic target is low, or that the value is
>> really 'rare'.
>
> It's possible, but (1) most people are running autovacuum these days,
> in which case this isn't likely to occur and (2) most people do not
> manage to expand the size of a table by five orders of magnitude
> without analyzing it. Generally these kinds of problems come from bad
> selectivity estimates.
>

Also, with partitioning the "combined" statistics of multiple tables is just
plain wrong much of the time. It makes some worst case assumptions about
the number of distinct values when merging multiple table results (even with
100% overlap and all unique values in the stats columns), and at least in
8.3 (haven't looked in 8.4) the row width estimate is the max of all the
child tables, not an average or weighted average. So even with 100% perfect
statistics on each individual table, do a scan over a few dozen partitions
(or a couple hundred) and the summary stats can be way off. The tendency is
to sometimes significantly overestimate the number of distinct values.

> In this case, though, I think that the actual number is less than the
> estimate because of the limit node immediately above. The problem is
> just that a top-N heapsort requires scanning the entire set of rows,
> and scanning 8 million rows is slow.
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>