Re: Index over only uncommon values in table

Steven Schlansker-3 wrote
> 1) The common value is not known at schema definition time, and may change
> (very slowly) over time.
>
> 2) JDBC uses prepared statements for everything, and the value to be
> selected is not known at statement prepare time, so any partial indices
> are ignored (this is a really really obnoxious behavior and makes partial
> indices almost useless combined with prepared statements, sadly…)

I'm not conversant enough to explain, in recent versions of PostgreSQL,
where this behavior has been modified so that parameter values are indeed
taken into account by the planner.

Thinking out loud here...

For your partial-index solution I guess you would have to implement a
routine where you query the statistics table for the most common values
array and the create a partial index where those values are excluded.
Periodically you would have to create a new index as the most frequent
values change. To get the planner to use said partial index you would have
to append the same "NOT IN ('a','b','c')" clause to the query that you use
to construct the index. Solving the planner and index problem at the same
time you should consider encapsulating this logic in a view that you can
replace as necessary.

You should include the version of PostgreSQL you are using since possible
solutions will vary depending on the presence of newer features.

Dave

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-over-only-uncommon-values-in-table-tp5759735p5759743.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.