Hacker News Reader

Comment by photon137 | Hacker News Reader

> index only scans will do wonders for append-mostly workloads

Won't updates be actually slower once indexes are added to take advantage of index-only scans?

Another issue that I can see comes not from having the index-only scan feature (which is cool, though belated) but having a query planner intelligent enough to figure out which index to hit - ie query optimization.

If the columns used in WHERE clause do not contain the appropriate columns (particularly for non-clustered indexes), shouldn't index hints be supported to help pick up the relevant index or would we end up with horrible execution plans leading to tablescans?([1] - I see this as a case of devs overriding users)

[1]: http://it.toolbox.com/blogs/database-soup/why-postgresql-doe...

View on HN

Yes, updates will be slower if you add more indexes, but if you're doing significantly more reads than writes then it's not much of an issue.

Also Postgres' query planner has been pretty smart about indexes for a long time, and its index format is pretty good as well. For example, if you have table (a, b, c) and index (b, c) Postgres can use the index to filter c by itself, which SQL Server certainly can't.

This is normally because indexes are sorted and hierarchical b-trees - it's ordered by b then c, so c is not ordered. However, a since the index could be smaller than the table itself it might be faster to scan every record of the index than the table.

Postgres' query planner is a thing of wonder.

This is a horribly naive theory (I have no idea what I'm talking about) but if you have foreign keys you need to get those indexed anyway (otherwise inserts or updates will need to do a sequential scan), and with index-only scans, if the referenced key exists in the index, the tuple won't have to be visited, hence inserts and updates getting faster in tables with foreign keys?

I think that's a very keen insight. There may be a detail that gets in the way, but nothing comes to mind at the moment. Did you know that PostgreSQL's referential integrity checks are -- in the most literal sense -- implemented as triggers that construct SQL, and then plan and execute them just like a client program?

http://doxygen.postgresql.org/ri__triggers_8c_source.html#l0...

I don't think anyone has measured the effect, which seems it would be greatest on tables with wide rows, whereby the wide rows would cause cache thrashing but the smaller index would not.

"shouldn't index hints be supported"

Index hints already are supported in multiple different ways:

http://thoughts.davisjeff.com/2011/02/05/why-postgresql-alre...

"I see this as a case of devs overriding users"

I don't think that's an accurate characterization. I'm sure there are some improvements to hinting that would be approved if they are well thought out and don't introduce major maintainability concerns, etc. Someone just needs to do the work required, including considering many alternative approaches which may be radically different from what you typically think of as hints.

The proposals that tend to fail are ones that follow an argument pattern like "Oracle does it this way... therefore it is good... therefore postgres should do something similar".

I'm not trying to dismiss your point at all. I think it's important that postgres give users as much power as possible, and a part of that is having some control over the optimizer without waiting for a new release. Postgres offers that in some innovative ways, but it could do better.

Reply on news.ycombinator.com