Re: Order by and timestamp - Mailing list pgsql-general
| From | Björn Lundin |
|---|---|
| Subject | Re: Order by and timestamp |
| Date | |
| Msg-id | 72890A04-294E-4753-8839-69F0BAD9AA17@gmail.com Whole thread Raw |
| In response to | Re: Order by and timestamp (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Order by and timestamp
Re: Order by and timestamp |
| List | pgsql-general |
16 mars 2020 kl. 16:46 skrev Adrian Klaver <adrian.klaver@aklaver.com>:On 3/16/20 3:03 AM, Björn Lundin wrote:I realize that I have (basically) the same dataset on another machine.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?
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@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@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@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@192.168.1.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@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=>
pgsql-general by date: