6 ms·

I think the bigger deal (at least for most existing deployments) are the index-only scans. This is one of the only features of Oracle that I missed after switch

by gphil 14y ago

I think the bigger deal (at least for most existing deployments) are the index-only scans. This is one of the only features of Oracle that I missed after switching to Postgres.

benhoyt 14y ago
Can you please explain index-only scans, and what the advantages are (or why this isn't the default already)?
- gphil 14y ago
  Sure. It's not the default already because the functionality did not exist at all in previous versions. Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like: select column4 from table where column3 = x; the DB will only read from the index (and not look at the table at all.) This is much faster as the index is much smaller in size than the table.
  - benhoyt 14y ago
    Thanks, that's interesting. So it only kicks in when your index covers all the columns in the "what" (select) clause. That tends not to be true for tables I've designed/worked with. I guess you could index differently to take advantage of this though.
    - jeltz 14y ago
      Yes, databases which support index-only scans tend to encourage this pattern of adding extra columns in your indexes. In some cases this can be a big gain, but larger indexes means that the indexes are slower for queries which do not need the extra columns or need to read the entire row anyway. Another set of queries which benefit from index only scans are those with EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they always needed to read part of the actual row to check the visibility information. In 9.2 it will for VACUUMED pages only need to check the index and the visibility map.
- harshreality 14y ago
  It's not the default already because pre-9.2 the MVCC implementation in Postgres didn't guarantee that an index has the same visibility as the table data. http://rhaas.blogspot.com/2010/11/index-only-scans.html http://rhaas.blogspot.com/2010/11/index-only-scans.html
j-kidd 14y ago
The title of the linked article is actually "PostgreSQL 9.2 beta improves scalability, adds JSON". Not sure why it get truncated. I, for one, am not excited by the JSON addition at all. On the other hand, index-only scanning, scalability improvement, and reduced power consumption are all very big deal.
jpitz 14y ago
I don't know. Index-only scans bring a level of feature parity with Oracle. Range types / range exclusion constraints introduce features that no other DBMS has - and solves a messy, hard problem that you pretty much have to hand-code for each scenario. One of these is legitimately revolutionary, and it isn't covering indexes.
- gphil 14y ago
  I have to admit that I am not as familiar with this feature, so I will check it out. I glossed over it as something I could already accomplish with a check constraint or a trigger, but I think you might be right that there's more to it than that.
  - jeltz 14y ago
    The reason a trigger or check constraint is not enough is race conditions. While the trigger or check constraint looks at the other rows in the table for a colliding range another transaction may at the same time insert a colliding range. The two concurrent transactions inserting ranges wont see each other and both will be inserted. Without PostgreSQL's exclusion constraints this can be solved in various more or less ugly and problem specific ways. Like locking the entire table, allowing overlapping ranges and cleaning them up later, implementing an own locking infrastructure (e.g. have one lock per day or per room), only having one process inserting at the same time, or just ignoring the problem and hoping you wont get any race conditions. Exclusion constraints is a clean solution to a messy problem.

by gphil 14y ago

I think the bigger deal (at least for most existing deployments) are the index-only scans. This is one of the only features of Oracle that I missed after switching to Postgres.

benhoyt 14y ago

Can you please explain index-only scans, and what the advantages are (or why this isn't the default already)?

gphil 14y ago
Sure. It's not the default already because the functionality did not exist at all in previous versions. Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like: select column4 from table where column3 = x; the DB will only read from the index (and not look at the table at all.) This is much faster as the index is much smaller in size than the table.
- benhoyt 14y ago
  Thanks, that's interesting. So it only kicks in when your index covers all the columns in the "what" (select) clause. That tends not to be true for tables I've designed/worked with. I guess you could index differently to take advantage of this though.
  - jeltz 14y ago
    Yes, databases which support index-only scans tend to encourage this pattern of adding extra columns in your indexes. In some cases this can be a big gain, but larger indexes means that the indexes are slower for queries which do not need the extra columns or need to read the entire row anyway. Another set of queries which benefit from index only scans are those with EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they always needed to read part of the actual row to check the visibility information. In 9.2 it will for VACUUMED pages only need to check the index and the visibility map.
harshreality 14y ago
It's not the default already because pre-9.2 the MVCC implementation in Postgres didn't guarantee that an index has the same visibility as the table data. http://rhaas.blogspot.com/2010/11/index-only-scans.html http://rhaas.blogspot.com/2010/11/index-only-scans.html

j-kidd 14y ago

The title of the linked article is actually "PostgreSQL 9.2 beta improves scalability, adds JSON". Not sure why it get truncated. I, for one, am not excited by the JSON addition at all. On the other hand, index-only scanning, scalability improvement, and reduced power consumption are all very big deal.

jpitz 14y ago

I don't know. Index-only scans bring a level of feature parity with Oracle. Range types / range exclusion constraints introduce features that no other DBMS has - and solves a messy, hard problem that you pretty much have to hand-code for each scenario. One of these is legitimately revolutionary, and it isn't covering indexes.

gphil 14y ago
I have to admit that I am not as familiar with this feature, so I will check it out. I glossed over it as something I could already accomplish with a check constraint or a trigger, but I think you might be right that there's more to it than that.
- jeltz 14y ago
  The reason a trigger or check constraint is not enough is race conditions. While the trigger or check constraint looks at the other rows in the table for a colliding range another transaction may at the same time insert a colliding range. The two concurrent transactions inserting ranges wont see each other and both will be inserted. Without PostgreSQL's exclusion constraints this can be solved in various more or less ugly and problem specific ways. Like locking the entire table, allowing overlapping ranges and cleaning them up later, implementing an own locking infrastructure (e.g. have one lock per day or per room), only having one process inserting at the same time, or just ignoring the problem and hoping you wont get any race conditions. Exclusion constraints is a clean solution to a messy problem.