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

From: Marko Tiikkaja <marko(at)joh(dot)to>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #8598: Row count estimates of partial indexes
Date: 2013-11-17 19:55:30
Message-ID: 52891F32.8010902@joh.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On 11/17/13, 5:29 PM, Tom Lane wrote:
> marko(at)joh(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2013-11-17 20:18:39 Re: BUG #8598: Row count estimates of partial indexes
Previous Message Tom Lane 2013-11-17 16:29:15 Re: BUG #8598: Row count estimates of partial indexes