Re: Order by and timestamp

>> Yeah, it's hard to think of any explanation other than "the query used a
>> corrupt index on startts to produce the ordering". But your \d doesn't
>> show any index on startts. So maybe there's more than one amarkets
>> table?

I realize that I have (basically) the same dataset on another machine.

bnl=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

bnl(at)tp:~$ uname -a
Linux tp 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

It misses som later record (from 2020) but otherwise contains the same data, and same definition
It is also the only user-database on the system

bnl=# \d amarkets
Table "public.amarkets"
Column | Type | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+------------------------
marketid | character varying(11) | | not null | ' '::character varying
marketname | character varying(50) | | not null | ' '::character varying
startts | timestamp(3) without time zone | | not null |
eventid | character varying(11) | | not null | ' '::character varying
markettype | character varying(25) | | not null | ' '::character varying
status | character varying(50) | | not null | ' '::character varying
betdelay | integer | | not null | 1
numwinners | integer | | not null | 1
numrunners | integer | | not null | 1
numactiverunners | integer | | not null | 1
totalmatched | numeric(15,2) | | not null | 0.0
totalavailable | numeric(15,2) | | not null | 0.0
ixxlupd | character varying(15) | | not null | ' '::character varying
ixxluts | timestamp(3) without time zone | | not null |
Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)

This gets it correctly.

So it points to something on the first machine.
Recreating indexes is a possibility, but (to me) a bit unintuitive since there are no index on startts
I’ll do that tomorrow.

--
Björn Lundin
b(dot)f(dot)lundin(at)gmail(dot)com