[sorkin2]

> One kind of significant downside of partial indexes in PostgreSQL is that they don't work with parameters.

Your example is fundamentally different from parameters sent by a client in a parameterized query, i.e. $1, $2, etc. Also while your flawed example will never use a partial index, parameterized queries can and often will do that. With prepared statements (which are a slightly different concept from parameterized queries) the devil will lie in the details, but even those don't really prevent partial indexes from being used since postgres version 9.2, released years ago.

[alt_f4]

my `flawed` example is fine for what I'm using it for: demonstrating that PSQL won't use a partial index in an obviously common use case. Feel free to argue about the semantics of what is a prepared statement vs parametrized query vs whatever, but the fact is that the PSQL planner does not evaluate the subquery select prior to execution nor is it capable to alter its original plan after execution of the subquery.

This makes partial indexes of EXTREMELY limited use for complex queries which often need to pass a non-literal or do a join on a partially indexed column.

In fact, it is this EXTREMELY inconvenient limitation that often forces people to rearchitect large tables around a set of smaller tables with inheritance and check constraints, so that the planner will do index scans on the smaller tables, rather than a huge index.

In fact, if partial indexes were properly architected and supported, it would eliminate 90% of the use cases for partitions in PSQL. Partitions, by the way, are also a sham in PSQL 10, merely putting some syntactic sugar over table inheritance and what is basically a table-routing insert/update trigger. The current planner support for them is a joke / non-existent / worse than roll-your-own inheritance with check constraints and triggers.