Richard Foote's Blogs have a lot of details about different types of Indexes that are available in Oracle : https://richardfoote.wordpress.com/category/index-internals/
Postgres Indexes for Newbies
31–40 of 43 posts
Re: Postgres Indexes for Newbies
#32For existing table, HypoPG is a very nice extension that can help you check if your index is helpful before actually creating it.
Re: Postgres Indexes for Newbies
#33Use the index, Luke is always a great resource. [0] https://use-the-index-luke.com/
Another amazing resource are the CMU Database Systems videos with Prof. Andy Pavlo. Videos from 2021 with a different professor (Andrew Crotty) have also been uploaded, but Pavlo is just so engaging. https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_... (Pavlo, 2019) https://www.youtube.com/playlist?list=PLSE8ODhjZXjZaHA6QcxDf... (Crotty, 2021)
But there is still no better resource for technical details in Postgres than this series https://habr.com/en/company/postgrespro/blog/441962/
Re: Postgres Indexes for Newbies
#34Earlier quoted context omitted.
If there's an index and the query planner is choosing sequential scan, it's doing so because it thinks that the random access pattern for the indexed data is going to be slower than the sequential access for the whole dataset. It's almost definitely right. So why generate the data? It will sort itself out when there's enough real data in there. You can also: - disable "enable_seqscan" - increase "seq_page_cost" - dec…
IME almost every query that takes way longer than expected in PG has been because it elected to do a sequential scan instead of using an index. I've set both random_page_cost and seq_page_cost to 1 for a database on an SSD (where sequential is still faster than random) but PG still chooses sequential scans. I selectively disable sequential scans on the connection ahead of badly planned queries now.
If your data changes a lot, try REINDEX and VACUUM ANALYZE on the tables involved and then test and check if the query planner still wants to do seq scan.
We have a couple of rather large tables with a lot of changes, where is’s needed regularly to ensure the query planner makes the best possible plans.
Re: Postgres Indexes for Newbies
#35Earlier quoted context omitted.
It depends on a lot of factors, my unscientific rule of thumb is that if the data doesn't fit in the RAM of the server, then that's when you may start worrying about how the data will be fetched from the disk when you query it (if performance matters). The problem case is when the data needed to serve an average query is "scattered across the disk". This means that you will need to potentially fetch way more informat…
> is the typical mechanism to sort the data on the disk in RDBMS SQL Server and Oracle both default to heaps (though it's rare to see a SQL Server table without a clustered index).
> SQL Server and Oracle both default to heaps
I don't think that's true for SQL Server.
If you define a primary key in SQL Server, this is automatically a clustered index.
Re: Postgres Indexes for Newbies
#36Earlier quoted context omitted.
For all of its greatness and (mostly well deserved) praises, the lack of a reasonable cluster index capability (as in data order at storage layer) is Postgres' biggest limit IMHO. Unfortunately, The CLUSTER command not only "blocks" the table for WRITE ops, but more importantly, it also blocks READ operations [0]. pg_repack helps, but is not always available when using a managed PG offering. Not being able to control…
Starting with PostgreSQL 11, B-tree indexes can store additional "non-key" columns in the index. These are kept in the index alongside the sorted key columns. This can avoid expensive sorts in certain query plans. It's not perfect. Stale table statistics sometimes forces PostgreSQL to check table pages to confirm that rows are visible to the current transaction. Index-only scans don't support features like expression…
Is there a way to get this behavior with the implicit index on the primary key?
Re: Postgres Indexes for Newbies
#37Earlier quoted context omitted.
Any idea what that size is? When should one be worried?
It depends on a lot of factors, my unscientific rule of thumb is that if the data doesn't fit in the RAM of the server, then that's when you may start worrying about how the data will be fetched from the disk when you query it (if performance matters). The problem case is when the data needed to serve an average query is "scattered across the disk". This means that you will need to potentially fetch way more informat…
> Cluster Index (or Indexed Views as it's called in SQL Server) is the typical mechanism to sort the data on the disk in RDBMS. MySQL does that by default with the primary key, but not Postgres.
Does that mean that Mysql moves the second or the first half of the table if you insert a row in the middle? I can't imagine that.
I've recently considered clustering multiple tera bytes of time-ordered data stored >100 partitions in a Postgres 12 instance. After careful consideration I came to the conclusion it wasn't worth it. Clustering would have sorted all the rows by date in the table blocks. But that doesn't guarantee anything about the block layer below the filesystem. So it is of dubious value from the standpoint of performance. The other advantage I was hoping for was being able to use a BRIN index. But since, my database has very rare cases of updates of those rows. A BRIN index looses its value very fast. Either I lower the fillrate to leave space in every block for updates. Which allows the BRIN index to stay current but costs a lot of space. Or I would have to force BRIN index updates regularly because they can be lossy. And that is not acceptable in my application. The whole database is stored on nvme disks managed by zfs. That won't benefit from ordering the data on some arbitrary abstraction in the middle.
Re: Postgres Indexes for Newbies
#38I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like. I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
A BRIN index is basically a minimum and maximum value in the indexed column for each megabyte of table.
Had a question and curious if you know. Could an updated_at column work theoretically better than a created_at column if there are some deletes and updates? Would updated_at be ordered as they are on disk?
Re: Postgres Indexes for Newbies
#39Earlier quoted context omitted.
> is the typical mechanism to sort the data on the disk in RDBMS SQL Server and Oracle both default to heaps (though it's rare to see a SQL Server table without a clustered index).
> SQL Server and Oracle both default to heaps I don't think that's true for SQL Server. If you define a primary key in SQL Server, this is automatically a clustered index.
Any table in SQL Server without a clustered index will be stored as a heap.
https://docs.microsoft.com/en-us/sql/relational-databases/in...
Re: Postgres Indexes for Newbies
#40Earlier quoted context omitted.
Another amazing resource are the CMU Database Systems videos with Prof. Andy Pavlo. Videos from 2021 with a different professor (Andrew Crotty) have also been uploaded, but Pavlo is just so engaging. https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_... (Pavlo, 2019) https://www.youtube.com/playlist?list=PLSE8ODhjZXjZaHA6QcxDf... (Crotty, 2021)
But there is still no better resource for technical details in Postgres than this series https://habr.com/en/company/postgrespro/blog/441962/
These are great, thank you.