Re: Order by and timestamp

> 16 mars 2020 kl. 16:46 skrev Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>
> On 3/16/20 3:03 AM, Björn Lundin wrote:
>>>> 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.
>
> Which brings me back to your first post where you had:
>
> Timing is on.
> AUTOCOMMIT off
> psql (9.6.10)
> Type "help" for help.
>
> Then you said the database was:
>
> version
> ------------------------------------------------------------------------------------------------
> PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
> (1 rad)
>
> Which seemed to be confirmed by:
>
> bnl(at)ibm2:~$ psql
> Tidtagning är på.
> AUTOCOMMIT off
> psql (9.6.15, server 9.4.15)
> Skriv "help" för hjälp.
>
>
> That leaves me wondering how you got to the output in the first post?

Ooh - terrible sorry.
The output from first post describing the database schema
Was actually from my production machine - a raspberry pi.
The pi hold a db on an usb-disk, which is pg_dump()ed every night and imported to ibm2 history db (the bad one)

The schema is identical to the one with trouble - which is a history database
Intended for testing

I did not realize that would matter when posting - did the post away from home,
I can reach the prod machine but not the history machine (ibm2) from outside.

So - from the pi - first post

bnl=# \q
bnl(at)pibetbot:~ $ psql
Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# \d amarkets
Table "public.amarkets"
Column | Type | Modifiers
------------------+--------------------------------+-----------------------------------------
marketid | character varying(11) | not null default ' '::character varying
marketname | character varying(50) | not null default ' '::character varying
startts | timestamp(3) without time zone | not null
eventid | character varying(11) | not null default ' '::character varying
markettype | character varying(25) | not null default ' '::character varying
status | character varying(50) | not null default ' '::character varying
betdelay | integer | not null default 1
numwinners | integer | not null default 1
numrunners | integer | not null default 1
numactiverunners | integer | not null default 1
totalmatched | numeric(15,2) | not null default 0.0
totalavailable | numeric(15,2) | not null default 0.0
ixxlupd | character varying(15) | not null default ' '::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)

bnl=# \q
bnl(at)pibetbot:~ $ logout
Connection to 192.168.1.7 closed.

From the machine (ibm2) with bad sort order

imac:~ bnl$ ssh 192.168.1.20
bnl(at)192(dot)168(dot)1(dot)20's password:
Linux ibm2 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
No mail.
Last login: Mon Mar 16 16:54:56 2020 from 192.168.1.174
bnl(at)ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \d amarkets
Tabell "public.amarkets"
Kolumn | Typ | Modifierare
------------------+--------------------------------+------------------------------------------
marketid | character varying(11) | inte null default ' '::character varying
marketname | character varying(50) | inte null default ' '::character varying
startts | timestamp(3) without time zone | inte null
eventid | character varying(11) | inte null default ' '::character varying
markettype | character varying(25) | inte null default ' '::character varying
status | character varying(50) | inte null default ' '::character varying
betdelay | integer | inte null default 1
numwinners | integer | inte null default 1
numrunners | integer | inte null default 1
numactiverunners | integer | inte null default 1
totalmatched | numeric(15,2) | inte null default 0.0
totalavailable | numeric(15,2) | inte null default 0.0
ixxlupd | character varying(15) | inte null default ' '::character varying
ixxluts | timestamp(3) without time zone | inte null
Index:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)

bnl=>

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