[smilliken]

To each their own. The fact that CTEs are optimization fences in PostgreSQL is one of its dearest features for me: it allows me to insist on a particular execution plan by lifting an index scan into its own CTE. This is important specifically when there's a lot of data and I can't take a chance with the planner getting it wrong.

[paulryanrogers]

This is why I wish PostgreSQL just had explicit hints. Their argument against is sensible as it does increase the maintenance cost, but when features become defacto planner hints then readability suffers more.

[smilliken]

I'm surely not the first to think of this idea: but wouldn't it be nice if we could just select from an index directly? Postgresql already represents indices as relations. Then we don't need to make comments executable, we preserve the spirit of SQL semantics, and give the user control to insist on a particular index.

[netcraft]

It was different in a few ways, but I used to do that with db2 on the as/400, you had physical files (tables) and logical files (indexes) and you could select directly from the logical files - but they were more like materialized views that were sorted in a particular way than exactly an index.

But I agree, it would be a nice ability to have.

[cneumuel]

That's not how it's supposed to work. You want DBAs to be able to tune the existing data model with indexes and materialized views. The optimizer should be smart enough to rewrite queries and, in your case, look up the necessary data from the index only. I don't know Postgresql, Oracle's optimizer has been doing that for years.