From: Greg Stark <gsstark@mit.edu>
To: Alex Adriaanse <alex@innovacomputing.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad row estimates
Date: 08 Mar 2006 12:37:07 -0500
Message-ID: <87r75cn8ho.fsf@stark.xeocode.com> (raw)
In-Reply-To: <440F1115.4020400@innovacomputing.com>
References: <4408E91C.90104@innovacomputing.com>
<87wtfasng0.fsf@stark.xeocode.com>
<14850.1141488599@sss.pgh.pa.us>
<87fylyrsfy.fsf@stark.xeocode.com>
<440F1115.4020400@innovacomputing.com>
Alex Adriaanse <alex@innovacomputing.com> writes:
> Its row estimates are still way off. As a matter of fact, it almost seems as
> if the index doesn't affect row estimates at all.
Indexes normally don't affect estimates. Expression indexes do effectively
create a new column to generate stats for, but that doesn't really help here
because there aren't any estimation functions for the geometric gist indexes.
> -> BitmapAnd (cost=8.99..8.99 rows=1 width=0) (actual time=0.485..0.485 rows=0 loops=135)
> -> Bitmap Index Scan on test_table_2_s_id (cost=0.00..2.17 rows=48 width=0) (actual time=0.015..0.015 rows=1 loops=135)
> Index Cond: (s_id = 13300613::numeric)
> -> Bitmap Index Scan on test_table_2_n_id (cost=0.00..6.57 rows=735 width=0) (actual time=0.467..0.467 rows=815 loops=135)
> Index Cond: ("outer".id = test_table_2.n_id)
If this query is representative then it seems you might be better off without
the test_table_2_n_id index. Of course this could be a problem if you need
that index for other purposes.
I'm puzzled how test_table_2_s_id's estimate isn't more precise. Are there
some values of s_id that are quite common and others that are unique? You
might try raising the statistics target on s_id.
Incidentally, 70ms is pretty good. I'm usually happy if all my mundane queries
are under 100ms and the more complex queries in the vicinity of 300ms. Trying
to optimize below 100ms is hard because you'll find a lot of variability in
the performance. Any extraneous disk i/o from checkpoints, vacuums, even other
services, will throw off your expectations.
--
greg
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: gsstark@mit.edu, alex@innovacomputing.com
Subject: Re: Bad row estimates
In-Reply-To: <87r75cn8ho.fsf@stark.xeocode.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