Search Postgresql Archives

Re: Order by and timestamp

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





16 mars 2020 kl. 16:46 skrev Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

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@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=> 





--
Björn Lundin





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux