Re: BUG #8598: Row count estimates of partial indexes

On 11/17/13, 5:29 PM, Tom Lane wrote:
> marko@joh.to writes:
>> We have the following partial index on a small subset of a larger table:
>>    "index_transactions_transaction_balance_details" btree (transactionid)
>> WHERE NOT processed AND accountbalancesdailyid IS NOT NULL
>> However, querying with the WHERE clause completely ignores the
>> pg_class.reltuples value for the index:
>
> Yup.  Row count estimates are derived by estimating the selectivity of the
> given WHERE clauses and multiplying by the (estimated) current table size.
> In the particular case you show here, with a partial index that *exactly*
> matches the WHERE clause, we could get a better answer by looking at the
> index size --- but that doesn't scale to any less simplistic case, such
> as a query with additional WHERE clauses.
>
> It's also important to realize that reltuples for an index is a whole lot
> less trustworthy than it is for a table; ANALYZE doesn't update the
> former, for example.  And scaling from the last-reported VACUUM stats
> to current reality is going to be shakier.
>
> So on the whole, I don't think this would be a good idea.

Any suggestions for a workaround?  When reading this index as a part of
a bigger query the horrible estimate ensures that nobody's having fun.
I currently have something like:

   SELECT * FROM
   (SELECT * FROM transactions WHERE <partial index> LIMIT 25000)
transactions
   JOIN ..

And I *really* don't like that as a workaround.


Regards,
Marko Tiikkaja