Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres Indexes for Newbies

blog.crunchydata.com

Post reply on HN
Print view View original

11–20 of 43 posts

Re: Postgres Indexes for Newbies

#11

Post by haimez »

eatonphil wrote: post #7
maweki wrote: post #5

I always first try to teach the intuition that an index is a different sorting of the data (with the full table scan being insertion order/sort by rowid). Everything that's faster with data sorted in this manner is faster in the database. That's for 2nd semester students who should have heard of sorted-list merging and binary search. That overlooks hash indexes or index-only queries, of course. Love the article but I…

> with the full table scan being insertion order/sort by rowid I'm not sure that makes sense. As far as I'm aware selecting without sorting has no guaranteed order across vendors at all.

No guaranteed order, but very typically a predictable order depending on the query plan chosen. If you’re relying on an ordering, you need to specify it in the query.

However, the scan order is predictable for a given engine depending on the query plan. If it’s doing an index scan, you’ll get results in index order and if it’s doing a table scan you’ll get it in table storage order (assuming a transactional, single node [edit: also single threaded] OLTP database- this goes out the window when outputs are aggregated across multi node (edit: or multi threaded) scans without an ORDER BY)

Re: Postgres Indexes for Newbies

#12

Post by throwaway8456 »

luhn wrote: post #4
mulmen wrote: post #2

I’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?

BRIN indexes don't affect the storage layer, so you need to make sure your table is appropriately sorted on disk through other means. CLUSTER can do this as a one-off job (pg_repack if you need to keep the table available for writes), but won't sort new data as it's written. https://www.postgresql.org/docs/current/sql-cluster.html https://reorg.github.io/pg_repack/

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 data ordering on disk is a potential deal breaker once the data reaches a certain size.

[0]: https://www.postgresql.org/docs/current/sql-cluster.html

Re: Postgres Indexes for Newbies

#13

Post by [deleted] »

throwaway8456 wrote: post #12
luhn wrote: post #4

Earlier quoted context omitted.

BRIN indexes don't affect the storage layer, so you need to make sure your table is appropriately sorted on disk through other means. CLUSTER can do this as a one-off job (pg_repack if you need to keep the table available for writes), but won't sort new data as it's written. https://www.postgresql.org/docs/current/sql-cluster.html https://reorg.github.io/pg_repack/

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…

[deleted]

Re: Postgres Indexes for Newbies

#15

Post by solresol »

candiddevmike wrote: post #10

One thing I struggle with when figuring out the best indexe is creating enough junk in the database to force it to use an index vs seq scan. Anyone have tips on good data generation tools that works with foreign keys and creates a good range of data? I've looked into using queries and generate_series but it doesn't work well with UUID keys (I think?) Right now I have a bash script that creates a bunch of entries thro…

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" - decrease "random_page_cost"

Re: Postgres Indexes for Newbies

#16

Post by haimez »

mulmen wrote: post #8
haimez wrote: post #6

Earlier quoted context omitted.

Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date. You are correct that they can be used for analytical workloads (but not exclusively, you still have other index types available and transactions, etc). They have very little performance overhead for the write path and small memory footprint compared to B-tree indexes. Howeve…

Interesting, thanks for sharing. I am comparing this mentally to Redshift's sort keys. It sounds like a BRIN index might approximate that behavior for the time dimension but not for a locale dimension (integer value, low cardinality, high volume). Do BRIN indices support compound keys? Sounds like that may not even be desirable even if they do. With a Postgres analytic table that has a time and locale dimension it so…

I don’t see any reason why it couldn’t support compound keys, assuming the combined min/max definition is in index column defined order. I have no idea if the Postgres implementation does though.

You’re also correct that a compound BRIN is questionably useful. If the first column in the key isn’t already an effective filter, you’re going to have to scan the whole table anyway in most cases- and if it is, then the cost of storing the min/max of subsequent columns is going to increase the overhead of the BRIN index in a way that seems unlikely to justify the benefit. It seems to me that the indexed columns would have to be mutually correlating in sort order for that to be useful (eg: created_date, created_time)

Re: Postgres Indexes for Newbies

#17

Post by dragonwriter »

mulmen wrote: post #8
haimez wrote: post #6

Earlier quoted context omitted.

Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date. You are correct that they can be used for analytical workloads (but not exclusively, you still have other index types available and transactions, etc). They have very little performance overhead for the write path and small memory footprint compared to B-tree indexes. Howeve…

Interesting, thanks for sharing. I am comparing this mentally to Redshift's sort keys. It sounds like a BRIN index might approximate that behavior for the time dimension but not for a locale dimension (integer value, low cardinality, high volume). Do BRIN indices support compound keys? Sounds like that may not even be desirable even if they do. With a Postgres analytic table that has a time and locale dimension it so…

> Do BRIN indices support compound keys?

Yes.

https://www.postgresql.org/docs/current/indexes-multicolumn....

“Currently, only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes.”

Re: Postgres Indexes for Newbies

#19

Post by barrkel »

haimez wrote: post #11
eatonphil wrote: post #7

Earlier quoted context omitted.

> with the full table scan being insertion order/sort by rowid I'm not sure that makes sense. As far as I'm aware selecting without sorting has no guaranteed order across vendors at all.

No guaranteed order, but very typically a predictable order depending on the query plan chosen. If you’re relying on an ordering, you need to specify it in the query. However, the scan order is predictable for a given engine depending on the query plan. If it’s doing an index scan, you’ll get results in index order and if it’s doing a table scan you’ll get it in table storage order (assuming a transactional, single n…

Postgres can do parallel table and index scans. Don't rely on order without explicit order by.

Re: Postgres Indexes for Newbies

#20

Post by barrkel »

solresol wrote: post #15
candiddevmike wrote: post #10

One thing I struggle with when figuring out the best indexe is creating enough junk in the database to force it to use an index vs seq scan. Anyone have tips on good data generation tools that works with foreign keys and creates a good range of data? I've looked into using queries and generate_series but it doesn't work well with UUID keys (I think?) Right now I have a bash script that creates a bunch of entries thro…

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.
Post reply on HN