PostgreSQL – How to ORDER BY Software Release Versions Like X.Y.Z

A few things you can do:

Use enums or lookups keyed by integer values, or a simple "char" field, instead of varchar sort keys where possible. I'd use an enum because you can control the sort order easily.

The only serious downside with an enum is that you can't currently drop values from an enum type. You can add them (including inserting them in the middle of the sort order) but not remove them. If that's a problem, you'll want to use a lookup table, or just a field declared "char" that has single character codes.

Also, if you don't need proper language collation, specify COLLATE "C" for character fields, e.g.

CREATE INDEX itemdesc_c ON supplier_management (item_description ASC COLLATE "C");

and then:

ORDER BY ...
    itemdesc COLLATE "C",
    ...

Important things to note:

• Pg can combine indexes for predicates (WHERE clauses etc) but not sorting. You can't use a bitmap index scan for a sort. So it can use at most one of the candidate indexes, then it has to sort the rows within each group.

• Low-selectivity indexes are a waste of time. If the values aren't widely distributed, don't index the column.

• Pg's doing an on-disk sort. Throw more memory at the problem - try SET work_mem = '20MB' to start with. But see my comments below re thrashing with high max_connections. Use a connection pool.

• Use a connection pool.

• Indexes have a cost - they slow down insert/update/delete and increase vacuum work. So if the index isn't being used lots, get rid of it.

• pg_catalog.pg_stat_user_indexes will help you tell which indexes are used.

• pg_stat_statements (in contrib) and pg_stat_plans (the latter is an external module) are very useful for capturing data about query patterns, slow queries, etc.

• Learn to love the auto_explain module.

Also, if you always do this sort, creating a composite index to match it will help.

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC
);

... but be aware that it's only useful for this particular sort, and it'll be a big index so it's only worth having if you do this a lot. In fact, you might as well add supplier_management.buyer_purchase_order_id too, so it can do an index-only scan:

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC,
      buyer_purchase_order_id
);

The answer for this simple case is Yes. Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order.

But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries with WHERE conditions or joins.

You might also get gaps or other rows mixed in if you have concurrent transactions writing to the same table at the same time. Unlikely, but possible.

There is no "natural" order in a database table. While the physical order of rows (which is reflected in the system column ctid) will correspond to their inserted order initially, that may change any time. UPDATE, DELETE, VACUUM and other commands can change the physical order of rows. But the generated values for id are stable and not in any way connected to that, of course.