From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: Ants Aasma <ants.aasma@eesti.ee>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Bogus startup cost for WindowAgg
Date: Wed, 13 Oct 2010 15:35:02 -0400
Message-ID: <4CB609E6.70800@vmsinfo.com> (raw)
In-Reply-To: <AANLkTin=Zy22T1o88DkRhCpzq50j44PNs9zesXb7Y_rK@mail.gmail.com>
References: <AANLkTin=Zy22T1o88DkRhCpzq50j44PNs9zesXb7Y_rK@mail.gmail.com>
Ants Aasma wrote:
> I hit an issue with window aggregate costing while experimenting with
> providing a count of the full match along side a limited result set.
> Seems that the window aggregate node doesn't take into account that it
> has to consume the whole input before outputting the first row. When
> this is combined with a limit, the resulting cost estimate is wildly
> underestimated, leading to suboptimal plans.
>
> Is this a known issue? I couldn't find anything referring to this on
> the mailing list or todo.
>
>
What is your histogram size? That's defined by the
default_statistics_target in your postgresql.conf.
Check the column histograms like this:
news=> select attname,array_length(most_common_vals,1)
from pg_stats
where tablename='moreover_documents_y2010m09';
attname | array_length
----------------------+--------------
document_id |
dre_reference |
headline | 1024
author | 212
url |
rank | 59
content | 1024
stories_like_this |
internet_web_site_id | 1024
harvest_time | 1024
valid_time | 1024
keyword | 95
article_id |
media_type | 5
source_type | 1
created_at | 1024
autonomy_fed_at | 1024
language | 37
(18 rows)
news=> show default_statistics_target;
default_statistics_target
---------------------------
1024
(1 row)
You will see that for most of the columns, the length of the histogram
array corresponds to the value of the default_statistics_target
parameter. For those that are smaller, the size is the total number of
values in the column in the sample taken by the "analyze" command. The
longer histogram, the better plan. In this case, the size does matter.
Note that there are no histograms for the document_id and dre_reference
columns. Those are the primary and unique keys, the optimizer can easily
guess the distribution of values.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mladen.gogala@vmsinfo.com, ants.aasma@eesti.ee
Subject: Re: Bogus startup cost for WindowAgg
In-Reply-To: <4CB609E6.70800@vmsinfo.com>
* 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