Message-Id: <48C4ED52.D078.0028.0@rentrak.com>
Date: Mon, 08 Sep 2008 09:16:16 -0700
From: "Matt Smiley" <mss@rentrak.com>
To: <mukhyala.kiran@gene.com>, <pgsql-performance@postgresql.org>
Subject: Re: inaccurate stats on large tables
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi Kiran,

You gave great info on your problem.

First, is this the query you're actually trying to speed up, or is it a =
simplified version?  It looks like the optimizer has already chosen the =
best execution plan for the given query.  Since the query has no joins, we =
only have to consider access paths.  You're fetching 58221/37909009 =3D =
0.15% of the rows, so a sequential scan is clearly inappropriate.  A basic =
index scan is likely to incur extra scattered I/O, so a bitmap index scan =
is favored.

To improve on this query's runtime, you could try any of the following:

 - Reorganize the data to reduce this query's scattered I/O (i.e. cluster =
on "paliasorigin_search3_idx" rather than "paliasorigin_alias_casefold_idx"=
).  Bear in mind, this may adversely affect other queries.

 - Increase the cache hit frequency by ensuring the underlying filesystem =
cache has plenty of RAM (usually so under Linux) and checking that other =
concurrent queries aren't polluting the cache.  Consider adding RAM if you =
think the working set of blocks required by most queries is larger than =
the combined Postgres and filesystem caches.  If other processes than the =
db do I/O on this machine, consider them as resource consumers, too.

 - Restructure the table, partitioning along a column that would be useful =
for pruning whole partitions for your painful queries.  In this case, =
origin_id or tax_id seems like a good bet, but again, consider other =
queries against this table.  38 million rows probably makes your table =
around 2 GB (guessing about 55 bytes/row).  Depending on the size and =
growth rate of the table, it may be time to consider partitioning.  Out of =
curiosity, what runtime are you typically seeing from this query?  The =
explain-analyze ran in 113 ms, which I'm guessing is the effect of =
caching, not the runtime you're trying to improve.

 - Rebuild the indexes on this table.  Under certain use conditions, btree =
indexes can get horribly bloated.  Rebuilding the indexes returns them to =
their most compact and balanced form.  For example: reindex index =
"paliasorigin_search3_idx";  Apart from the locking and CPU usage during =
the rebuild, this has no negative consequences, so I'd try this before =
something drastic like partitioning.  First review the current size of the =
index for comparison: select pg_size_pretty(pg_relation_size('paliasorigin_=
search3_idx'));

Since you asked specifically about improving the row-count estimate, like =
the previous responder said, you should consider increasing the statistics =
target.  This will help if individual columns are being underestimated, =
but not if the overestimate is due to joint variation.  In other words, =
the optimizer has no way to tell if there is there a logical relationship =
between columns A and B such that certain values in B only occur with =
certain values of A.  Just judging from the names, it sounds like =
origin_id and tax_id might have a parent-child relationship, so I thought =
it was worth mentioning.

Do the columns individually have good estimates?
explain analyze select * from paliasorigin where origin_id=3D20;
explain analyze select * from paliasorigin where tax_id=3D9606;

If not, increase the statistics on that column, reanalyze the table, and =
recheck the selectivity estimate:
alter table paliasorigin alter column origin_id set statistics 20;
analyze paliasorigin;
explain analyze select * from paliasorigin where origin_id=3D20;

Good luck!
Matt