Re: Order by and timestamp

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?

In other words different psql version and no server version listed which
indicates the server is 9.6.

>
> 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 <mailto:b(dot)f(dot)lundin(at)gmail(dot)com>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com