PostgreSQL Indexes: First principles
eftimov.net
eftimov.net
So many times I've fought or watched other developers fight with getting a DBMS to use an index. Or been surprised to find out that production doesn't have that index but staging does. Or found out that someone added a lower() to a query that made the index stop working. Or found out that index turns out to be more expensive than scanning the table directly in a circumstance that is different between instances of the same software.
Contrast that to an API like the Cassandra Thrift interface. You know if you're doing something expensive because the API only exposes the cheap things. It doesn't have an implicit sequential scan. You know you're doing one because you had to type out `while(next=...)` right in your code.
Something as simple as
SELECT mycolumn FROM mytable WHERE INDEXED b=5
SELECT mycolumn FROM mytable WHERE SCAN b=5
I bet it is pretty common. Personally, as long as the query is fast enough for my needs I don't care how it executes. I only start caring when performance sucks. I wouldn't be surprised if many other developers have a similar opinion.
And not just in transactional systems but also batch processing, e.g. why would I write mapreduce code if I can just use a system like Hive that gives me a nice SQL-based interface to my data?
Once you learn that DB performance is not a gradual thing, you get into the grove of it. It goes from good to horrible once you hit that magic memory / table size.
Truthfully, if your developers are the ones writing tools to figure out real-time performance, you probably want to start thinking about what's going on. (obviously this does not apply to the parent poster)
I'm 100% sure its common.
> Or been surprised to find out that production doesn't have that index but staging does.
That's the same problem as having different application code on staging then what gets deployed to production; this isn't a problem with queries working independent of indexes its a problem of broken release processes, which requiring more ceremony to make queries work will not fix.
> I'm 100% sure its common.
The nice thing about the current state of affairs is that you can first build your queries / views until you get the results you need, then look what indices you can add to improve the performance of those queries. And if you get the results you need and find that performance is acceptable, you can just omit (or at least delay) the final step.
Indeed! And sometimes it gets even more nuanced - when say the way the index(es) would be used would imply a lot of disk seeking (or page faults if not the whole of index(es) is/are in memory (you can "pre-warm" them into memory but if you have to do that you may have to go back to your schema and index design and re-assess it anyway)). Sometimes a bulldozer-like (mostly) (hopefully!) sequential disk read works better.
The latter becomes tricky with say SSDs - you then may need to inform Postgres of differing costs for (e.g.) disk seeks (or memory reads vs. disk reads) so it can better decide which way to go.
Consider the example with the ID's if he would have an index with all the fields he would need, example he would only need the id and the email he could create a index with them and the query would be faster, that would be a index only scan (https://wiki.postgresql.org/wiki/Index-only_scans)
Some searching and I found some blog posts suggesting lowering them both to closer to one on SSDs [2]. Indeed, the documentation says you can set them both to 1 if you are sure that the database will be fully cached in RAM.
[1] http://www.postgresql.org/docs/current/static/runtime-config... [2] http://www.cybertec.at/2013/01/better-postgresql-performance...
There's also `ALTER TABLE SET STATISTICS` and default_statistics_target config parameter (for supposedly smart query planning on the fly?) (though would have to re-read to recall how these things work - off to bed soon): http://www.postgresql.org/docs/current/static/runtime-config... (edit ah you included the same link - lots of good stuff here - ahh I like Postgres documentation)
"The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached
If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost."
In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.
Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk).
Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole..
For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes
Luckily SSDs have a much lower penalty for random reads compared to spinning disks, so in future this will be less of an issue.
In the meantime all you can do is use a different filesystem, hide the problem with cache, or manually defragment your data files from time to time.
If you're finding the blog post with id 10, then an index is preferable because your selectivity is low. The per-row cost is going to be higher than a seq scan, but since you can exclude most of the rows it can still be better.
If you're doing analytics, you often actually want to do as many seq scans as possible. The per-row cost of sequential access on disk is so much better: if you know you're going to want to read most rows, it's a mistake to force an index scan. It depends on the selectivity of the query.
However, index scans aren't the only way you could do low-selectivity queries. Amazon Redshift doesn't support index scans at all, but it keeps the data sorted on disk which often allows it to skip blocks as needed. The whole database is just seq scans and you have to spend a lot of effort designing your table structure to support it, but it's still blindingly fast when everything aligns. People don't use Redshift to grab 1 blog post at a time from the database, though.
This is no longer true in this generality, because since PostgreSQL 9.2 there are also index-only scans. Those scans take the data directly from the index, without going through the original rows at all. Of course, this works only under certain conditions. See also:
http://michael.otacoo.com/postgresql-2/postgresql-9-2-highli...
http://www.postgresql.org/docs/9.2/static/index-scanning.htm...
"If the index stores the original indexed data values (and not some lossy representation of them), it is useful to support index-only scans, in which the index returns the actual data not just the TID of the heap tuple. This will only work if the visibility map shows that the TID is on an all-visible page; else the heap tuple must be visited anyway to check MVCC visibility. But that is no concern of the access method's."
The stuff here is universal and applies to any database (perhaps with some terminology change).
There are also video and image (e.g. histogram) indexes in postgres as well, but they're much more rare.
http://www.postgresql.org/docs/current/static/sql-cluster.ht...
With a true clustered index, the table IS the index, this saves space and time by not having to maintain a secondary index and then looking up the result in the table. This can be very important especially with large datasets.
Really wish true clustered indexes where implemented in PG. Kind of odd the article doesn't mention this while starting out explaining clustered vs non-clustered.
So at first glance it's super-speedy for clustered index-like queries, but then becomes slower and slower over time as the data changes and consecutive index reads are no longer sequential on disk.