2

I have a table, lets call it mytable:

CREATE TABLE mytable {
    id INTEGER,
    ts TIMESTAMP WITHOUT TIME ZONE,
    ..
)

and I have following query in multiple client applications:

SELECT ... FROM mytable ... ORDER BY (ts, id) DESC;

The query is very slow, because of (ts, id) DESC ordering, which results in:

...
Sort Key: (ROW(t.ts, t.id))
Sort Method: top-N heapsort  Memory: 51kB
...

in EXPLAIN ANALYZE VERBOSE ..

When I remove the parentheses in ORDER BY and use .. ORDER BY ts DESC, id DESC; in the query, and add CREATE INDEX idx_mytable ON mytable(ts, id); it uses the index and it's super fast.

However - as I've said, the original query is in multiple clients and I would rather add one index than change client application and update all clients.

So I would like to create ROW VALUE index - something like:

CREATE INDEX idx_mytable ON mytable(ROW(ts, id));

but this is not a valid syntax.

Is there a way to create ROW VALUE index which would be used in query with ..ORDER BY (ts, id) DESC ?

  • The only sensible solution is to get rid of those useless (not to say stupid) parentheses. – a_horse_with_no_name Jul 26 '16 at 15:53
  • @a_horse_with_no_name I agree they are stupid (yes, I put them there few years ago - so it is entirely my fault) - but is there really no way to define "composite value/row value" index? I thought that because postgresql is the only DB which fully implements sql:92 row values, there may be some way.. – Jan Spurny Jul 26 '16 at 16:01
  • Did you try: CREATE INDEX ON mytable((ts, id)); – a_horse_with_no_name Jul 26 '16 at 16:03
  • @a_horse_with_no_name yes, I did (it says: ERROR: syntax error at or near ",") , I also tried (row(ts, id)) and many others but nothing seems to work - also from postgresql.org/docs/9.4/static/sql-createindex.html I think it would have to be some kind of expression in order for it to be a valid statement – Jan Spurny Jul 26 '16 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.