Re: Order by and timestamp - Mailing list pgsql-general
| From | Björn Lundin |
|---|---|
| Subject | Re: Order by and timestamp |
| Date | |
| Msg-id | 346C3B58-D29E-4CC2-8200-691ECA91F494@gmail.com Whole thread Raw |
| In response to | Re: Order by and timestamp (Björn Lundin <b.f.lundin@gmail.com>) |
| Responses |
Re: Order by and timestamp
|
| List | pgsql-general |
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@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)
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.
pgsql-general by date: