From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurent Raufaste <analogue@glop.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG planning randomly ?
Date: Wed, 27 Feb 2008 15:36:04 -0500
Message-ID: <7598.1204144564@sss.pgh.pa.us> (raw)
In-Reply-To: <669dc9710802270938j78d6fc80tc3e588ece1add4f8@mail.gmail.com>
References: <669dc9710802260719h61d846b3hd50b7f547bcb82c3@mail.gmail.com>
<3637.1204043502@sss.pgh.pa.us>
<669dc9710802260912y7fe58f9epe42fe7eb360119d8@mail.gmail.com>
<5296.1204048779@sss.pgh.pa.us>
<669dc9710802261205j42250581w9fcc8b2def694650@mail.gmail.com>
<8617.1204063036@sss.pgh.pa.us>
<669dc9710802270938j78d6fc80tc3e588ece1add4f8@mail.gmail.com>
"Laurent Raufaste" <analogue@glop.org> writes:
> On a random server, the plan before the ANALYZE was:
> Bitmap Heap Scan on _comment (cost=15833.00..440356.99 rows=155649
> width=0) (actual time=1.581..2.885 rows=1070 loops=1)
> Recheck Cond: (path <@ '0.1.14155763'::ltree)
> -> Bitmap Index Scan on gist_idx_comment_path
> (cost=0.00..15794.09 rows=155649 width=0) (actual time=1.552..1.552
> rows=1070 loops=1)
> Index Cond: (path <@ '0.1.14155763'::ltree)
> Total runtime: 3.160 ms
> The runtime is ok, but the planned cost is huge, because the row count
> of the index scan estimates 100x more rows. After the ANALYZE it was
> like the others. If this wrong row count happens, I understand why the
> planner try to find an alternative plan in the first query I showed
> you in a previous mail.
> How can I help him to better estimate the row count ? Setting
> default_stats_target to 1000 did not help =(
Are you sure the table had been analyzed recently at all on that server?
If it had, then what you must be dealing with is a different result from
a different random sample. The laws of statistics say that sometimes a
random sample won't be very representative ... but if the sample is
reasonably large they also say that won't happen very often. You could
try ANALYZEing over and over and seeing what rowcount estimate you get
after each one. If you frequently get a bad estimate, maybe it would be
worth looking at the pg_stats row for _comment.path to see if there's
anything obviously bogus about the bad samples.
regards, tom lane
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: tgl@sss.pgh.pa.us, analogue@glop.org
Subject: Re: PG planning randomly ?
In-Reply-To: <7598.1204144564@sss.pgh.pa.us>
* 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