Re: Order by and timestamp

[Björn Lundin <b.f.lundin@xxxxxxxxx>]

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

On 3/16/20 1:51 AM, Björn Lundin wrote:

16 mars 2020 kl. 01:41 skrev Tom Lane <tgl@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx>>:

Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

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?

Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty

Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.

bnl=> explain select * from amarkets order by startts;

Can you run as:

explain analyze select * from amarkets order by startts;

Yes, below the first plan

                              QUERY PLAN
-----------------------------------------------------------------------
 Sort  (cost=10702.57..10939.29 rows=94691 width=106)
   Sort Key: startts
   ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)
—

bnl=> explain analyze select * from amarkets order by startts;

                                                      QUERY PLAN                                                       

-----------------------------------------------------------------------------------------------------------------------

 Sort  (cost=10702.57..10939.29 rows=94691 width=106) (actual time=404.555..422.174 rows=97835 loops=1)

   Sort Key: startts

   Sort Method: quicksort  Memory: 24329kB

   ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106) (actual time=4.586..351.739 rows=97835 loops=1)

 Planning time: 74.707 ms

 Execution time: 434.785 ms

(6 rader)

Tid: 527,142 ms

bnl=> 

--

Björn Lundin

b.f.lundin@xxxxxxxxx