Re: Erratically behaving query needs optimization

On Sun, Aug 25, 2019 at 5:51 PM Jeff Janes <jeff.janes@gmail.com> wrote:
>
> Yes, it certainly looks like it is due to cold caches.  But you say it is slow at first, and then say it varies greatly during a run.  Is being slow at first the only way it varies greatly, or is there large variation even beyond that?

There is a great variation in run times (hundreds of ms to several
seconds) even beyond the start of the server.
The query runs several times with a different device_id, object_id and
another list of attribute_ids and it varies from one another.

> You can use pg_rewarm to overcome the cold cache issue when you first start up the server.

I cannot find anything related to pg_rewarm other than some dead ends
from 2013 from which I gather it only works on Linux.
Anyway, I have problems even beyond the start of the database, it's
just easier to reproduce the problem at the start, otherwise I have to
leave the application running for a while (to invalidate part of the
cache I think).

> If you query only on "results" with only the conditions that apply to "results", what is the expected number of rows, and what is the actual number of rows?

Explain for the query on results only: https://explain.depesz.com/s/Csau 

EXPLAIN (ANALYZE,BUFFERS)
 SELECT DISTINCT ON (results.attribute_id) results.timestamp,
results.data FROM results
 WHERE
     results.data <> '<NullData/>'
     AND results.data IS NOT NULL
     AND results.object_id = 1955
     AND results.attribute_id IN (4, 5) -- possibly a longer list here
     AND results.data_access_result = 'SUCCESS'
 ORDER BY results.attribute_id, results.timestamp DESC
 LIMIT 2 -- limit by the length of the attributes list

Limit  (cost=166793.28..167335.52 rows=2 width=54) (actual
time=134783.510..134816.941 rows=2 loops=1)
  Buffers: shared hit=19086 read=46836, temp read=1522 written=3311
  ->  Unique  (cost=166793.28..168420.01 rows=6 width=54) (actual
time=134783.507..134816.850 rows=2 loops=1)
        Buffers: shared hit=19086 read=46836, temp read=1522 written=3311
        ->  Sort  (cost=166793.28..167606.64 rows=325346 width=54)
(actual time=134783.505..134802.602 rows=205380 loops=1)
              Sort Key: attribute_id, "timestamp" DESC

So maybe I should de-normalize and place the device_id column into the
"results" table and add it to the index in your suggestion above?