4 ms·

Oracle like many other databases has clustered indexes. PostgreSQL does not and it can have big performance implications.

mmaurizi 8y ago
Are you sure about that? https://www.postgresql.org/docs/9.1/sql-cluster.html https://www.postgresql.org/docs/9.1/sql-cluster.html
- polskibus 8y ago
  That's not how a real clustered primary key works, because "the changes are not clustered.".
  - asah 8y ago
    can confirm. That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faster and all the columns are in there. I esp recommend reading about function and expression indexes, as well as the (new) covering indexes. http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-indexing-tricks-that-surprise-everyone http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-in... https://www.endpoint.com/blog/2013/06/10/postgresql-functional-indexes https://www.endpoint.com/blog/2013/06/10/postgresql-function... https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/ https://paquier.xyz/postgresql-2/postgres-11-covering-indexe...
    - polskibus 8y ago
      Surely the writes will be much slower if you have to copy the data to the index
      - Tostino 8y ago
        What do you think a (non clustered) index is? Of course it slows down writes.
        polskibus 8y ago
        It was a reply to proposed workaround, to show that it still has significant drawbacks
SigmundA 8y ago
Yes they really should look at adding index organized tables, aka real clustered indexes. Better space usage and performance for that single index since the table is the index, slightly worse performance for non clustered indexes due to a double index traversal.

mmaurizi 8y ago

Are you sure about that? https://www.postgresql.org/docs/9.1/sql-cluster.html https://www.postgresql.org/docs/9.1/sql-cluster.html

polskibus 8y ago
That's not how a real clustered primary key works, because "the changes are not clustered.".
- asah 8y ago
  can confirm. That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faster and all the columns are in there. I esp recommend reading about function and expression indexes, as well as the (new) covering indexes. http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-indexing-tricks-that-surprise-everyone http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-in... https://www.endpoint.com/blog/2013/06/10/postgresql-functional-indexes https://www.endpoint.com/blog/2013/06/10/postgresql-function... https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/ https://paquier.xyz/postgresql-2/postgres-11-covering-indexe...
  - polskibus 8y ago
    Surely the writes will be much slower if you have to copy the data to the index
    - Tostino 8y ago
      What do you think a (non clustered) index is? Of course it slows down writes.
      - polskibus 8y ago
        It was a reply to proposed workaround, to show that it still has significant drawbacks

SigmundA 8y ago

Yes they really should look at adding index organized tables, aka real clustered indexes. Better space usage and performance for that single index since the table is the index, slightly worse performance for non clustered indexes due to a double index traversal.