To: "Laurent Raufaste" <analogue@glop.org>
cc: pgsql-performance@postgresql.org
Subject: Re: PG planning randomly ? 
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>
Comments: In-reply-to "Laurent Raufaste" <analogue@glop.org>
	message dated "Wed, 27 Feb 2008 18:38:48 +0100"
Date: Wed, 27 Feb 2008 15:36:04 -0500
Message-ID: <7598.1204144564@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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