3 ms·

CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes

CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes a lot of trouble.) I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. This can save a lot of space for thin tables or tables with large keys, and would be basically an auto-CLUSTER with all the performance that comes with that. This is how MySQL works. The hash table primary storage is better sometimes but it sucks for range queries leading people to need timescale for good data locality

hardwaresofton 4y ago
> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. Postgres has this - they’re called “covering indexes” https://www.postgresql.org/docs/current/indexes-index-only-scans.html https://www.postgresql.org/docs/current/indexes-index-only-s... Note that there is a limit to index tuple size, which is lower than the limit on row size
- srcreigh 4y ago
  Covering indexes are different. You’d have to have a 2nd unique index which has all the rows from the table. A lot of large datasets would benefit from auto clustering but can’t handle 2x the storage for the same data.
  - hardwaresofton 4y ago
    Ah thank you for noting this -- combined with SigmundA's answer I think I get how they're different, and how covering indexes are not the same solution
- SigmundA 4y ago
  Not the same, this is called a clustered index or index organized tables. With a covering index in PG you still have a heap storing the rows and a copy of the covered columns in the index. With clustered indexes or IOT's the table is the index there is no duplication unless you have other secondary indexes. This save a lot of space and reduces indirection when seeking on the clustered index. Some DB's like Sqlite and InnoDB (MySQL) this is always the case the table is a b-tree and there is always a clustered index even if you don't define one explicitly. In others like Oracle or MSSQL you have a choice of unordered heap or b-tree, in PG you have no choice the table is always an unordered heap and all indexes are secondary.
  - hardwaresofton 4y ago
    That's for this explanation -- didn't see how they were different but your comment explains it perfectly. With this understanding I can't grasp the benefit of a clustered index -- if you're using the primary index (let's say numeric auto-incrementing ID) then you'd likely have a secondary index for that already (in PG). If you were searching by something else, the default clustered layout is a hindrance as you must traverse unnecessarily to find entries (rather than sequentially scan)... The only penalty of PG's decision seems to be excess memory usage (storing a second copy of the identifying tuple contents in memory) -- is that characterization correct? But I wonder how this holds up with a spinning disk -- I wouldn't want to follow indices (and do random reads) on spinning disks. Looking at the other side, I guess the case where PG shines is where you want to do batch processing (so looking at a page of tuples is good locality-wise, but you also often retrieve by the identifier, and don't mind paying the cost of [rows x identifier size] for the privilege. Are there some specific use cases where clustering indices clearly outperform/are the right choice?
    - SigmundA 4y ago
      If you primarily look up by the primary key which is pretty common the clustered index is faster than looking up the primary key in a standard index then finding the row in the heap for the other columns. If you make the standard indexing covering all your columns you have the behavior of a clustered index except the data is duplicated in the heap. Clustered indexes can save significant space on a narrow table with a lot of rows accessed in a particular way and perform better as well, if you access the table in multiple ways with other secondary indexes it can be slightly slower to access through a secondary.
    - srcreigh 4y ago
      The important factor is # of pages on disk that need to be retrieved. Secondary indexes (aka every index in Postgres) have to lookup the primary storage as well. If you use a primary key index to pull 100 rows, if the rows aren't clustered, then you're looking at ~300 pages needing to be pulled. That's roughly 2 per index traversal (the first level of the index is generally cached) plus one per row to pull from primary storage. This can be improved in two ways. One, if you add a second index which gives you better locality in the index. For example (customer_id, product_id) will group up all the rows by customer id. This can reduce the # of pages for index traversals down to <5 as long as each customer doesn't have a lot of rows. And in many cases this makes the primary index on just id useless. This brings the total down to 105 pages give or take. (depends on how many products each customer has) The other way is to use an actual primary index, or use a covering index so that the data you retrieve is already in the index. For example if you're just pulling product_name from your table, you can use covering index on (customer_id, id, product_name) so that the product_name has locality with the customer's product IDs. This would bring down the total pages to be retrieved down to maybe ~20, since product_name tends to be larger data. It's a question of how many (customer_id, product_id, product_name) tuples can fit on one 8KB page and how many products the customer has. If you use a primary index, the whole rows are on pages. This lets you run queries that pull lots of data (or different data) and have good data locality, but it means less tuples per row so you need more rows. So you'd access maybe ~50 rows but this index could cover a lot of queries unlike the covering index which only works for product_name. These days SSDs are much faster than hard drives, so # of rows pulled off disk is still important but not as much so. Another thing this buys you is that you don't pollute the in memory cache by evicting pages just to load a new page that doesn't get utilized well. For instance original the index leaf nodes that are just 4 bytes (product_id, rowid) so every one you throw away 99% of the data on that page.
jeltz 4y ago
> CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes a lot of trouble.) Nope, it does not write anything to disk unless you have more data than work_mem.
- srcreigh 4y ago
  Nice, TIL. The default value for work_mem is only 4MB though, and when I’ve seen materialized CTEs go wrong they’re usually using temp tables in the 10s or even 100s of MBs. Usually “with products as (select * from products where customer_id = 123)” type of stuff where some customers have hundreds of products with large rows.
mkleczek 4y ago
> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. It is coming: https://github.com/orioledb/orioledb https://github.com/orioledb/orioledb

hardwaresofton 4y ago

> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. Postgres has this - they’re called “covering indexes” https://www.postgresql.org/docs/current/indexes-index-only-scans.html https://www.postgresql.org/docs/current/indexes-index-only-s... Note that there is a limit to index tuple size, which is lower than the limit on row size

srcreigh 4y ago
Covering indexes are different. You’d have to have a 2nd unique index which has all the rows from the table. A lot of large datasets would benefit from auto clustering but can’t handle 2x the storage for the same data.
- hardwaresofton 4y ago
  Ah thank you for noting this -- combined with SigmundA's answer I think I get how they're different, and how covering indexes are not the same solution
SigmundA 4y ago
Not the same, this is called a clustered index or index organized tables. With a covering index in PG you still have a heap storing the rows and a copy of the covered columns in the index. With clustered indexes or IOT's the table is the index there is no duplication unless you have other secondary indexes. This save a lot of space and reduces indirection when seeking on the clustered index. Some DB's like Sqlite and InnoDB (MySQL) this is always the case the table is a b-tree and there is always a clustered index even if you don't define one explicitly. In others like Oracle or MSSQL you have a choice of unordered heap or b-tree, in PG you have no choice the table is always an unordered heap and all indexes are secondary.
- hardwaresofton 4y ago
  That's for this explanation -- didn't see how they were different but your comment explains it perfectly. With this understanding I can't grasp the benefit of a clustered index -- if you're using the primary index (let's say numeric auto-incrementing ID) then you'd likely have a secondary index for that already (in PG). If you were searching by something else, the default clustered layout is a hindrance as you must traverse unnecessarily to find entries (rather than sequentially scan)... The only penalty of PG's decision seems to be excess memory usage (storing a second copy of the identifying tuple contents in memory) -- is that characterization correct? But I wonder how this holds up with a spinning disk -- I wouldn't want to follow indices (and do random reads) on spinning disks. Looking at the other side, I guess the case where PG shines is where you want to do batch processing (so looking at a page of tuples is good locality-wise, but you also often retrieve by the identifier, and don't mind paying the cost of [rows x identifier size] for the privilege. Are there some specific use cases where clustering indices clearly outperform/are the right choice?
  - SigmundA 4y ago
    If you primarily look up by the primary key which is pretty common the clustered index is faster than looking up the primary key in a standard index then finding the row in the heap for the other columns. If you make the standard indexing covering all your columns you have the behavior of a clustered index except the data is duplicated in the heap. Clustered indexes can save significant space on a narrow table with a lot of rows accessed in a particular way and perform better as well, if you access the table in multiple ways with other secondary indexes it can be slightly slower to access through a secondary.
  - srcreigh 4y ago
    The important factor is # of pages on disk that need to be retrieved. Secondary indexes (aka every index in Postgres) have to lookup the primary storage as well. If you use a primary key index to pull 100 rows, if the rows aren't clustered, then you're looking at ~300 pages needing to be pulled. That's roughly 2 per index traversal (the first level of the index is generally cached) plus one per row to pull from primary storage. This can be improved in two ways. One, if you add a second index which gives you better locality in the index. For example (customer_id, product_id) will group up all the rows by customer id. This can reduce the # of pages for index traversals down to <5 as long as each customer doesn't have a lot of rows. And in many cases this makes the primary index on just id useless. This brings the total down to 105 pages give or take. (depends on how many products each customer has) The other way is to use an actual primary index, or use a covering index so that the data you retrieve is already in the index. For example if you're just pulling product_name from your table, you can use covering index on (customer_id, id, product_name) so that the product_name has locality with the customer's product IDs. This would bring down the total pages to be retrieved down to maybe ~20, since product_name tends to be larger data. It's a question of how many (customer_id, product_id, product_name) tuples can fit on one 8KB page and how many products the customer has. If you use a primary index, the whole rows are on pages. This lets you run queries that pull lots of data (or different data) and have good data locality, but it means less tuples per row so you need more rows. So you'd access maybe ~50 rows but this index could cover a lot of queries unlike the covering index which only works for product_name. These days SSDs are much faster than hard drives, so # of rows pulled off disk is still important but not as much so. Another thing this buys you is that you don't pollute the in memory cache by evicting pages just to load a new page that doesn't get utilized well. For instance original the index leaf nodes that are just 4 bytes (product_id, rowid) so every one you throw away 99% of the data on that page.

jeltz 4y ago

> CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes a lot of trouble.) Nope, it does not write anything to disk unless you have more data than work_mem.

srcreigh 4y ago
Nice, TIL. The default value for work_mem is only 4MB though, and when I’ve seen materialized CTEs go wrong they’re usually using temp tables in the 10s or even 100s of MBs. Usually “with products as (select * from products where customer_id = 123)” type of stuff where some customers have hundreds of products with large rows.

mkleczek 4y ago

> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. It is coming: https://github.com/orioledb/orioledb https://github.com/orioledb/orioledb