[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.