C[omp]ute

From: andrew cooke <andrew@...>

Date: Fri, 29 Sep 2023 12:54:40 -0300

I have finally understood a problem that has been worrying me for a long
time.  The key information is here -
https://dba.stackexchange.com/questions/170860

In my case I have tables that look like:

   publisher | rowid | other data
   ------------------------------
   puba      | 1     | ...
   puba      | 2     | ...
   puba      | 3     | ...
   pubb      | 1     | ...
   pubb      | 2     | ...

where:

 - there is a composite primary key (publisher, rowid)
 - the number of distinct publishers is small
 - the total number of entries is large

And the particular problem I had was in the query:

   select publisher, max(timestamp) from table group by publisher;

where timestamp is part of "other data".

This query was slow and NOT using an index on (timestamp, publisher)
(and the problem persists if the order in the index is swapped).

This was a big surprise, because the index seems perfect for the job -
it has ordered timestamps "grouped by" publisher (in a sense).

The problem is that Postgres does not exploit the knowledge that there
are only a few publishers.  So it decides to do a full scan to find
all publishers (simplifying a little).

Since my publishers were actually listed in the publisher table the
following query was much (100x) quicker:

   select p.publisher,
          (select max(t.timestamp) from table as t
                  where t.publisher = p.publisher)
          from publisher as p;

because it forces Postgres to look at each publisher in turn (instead
of scanning many many duplicates).


An extra detail is that I had to pull a more complex calculation based
on the timestamp into an outer query so that the "max" was clear
enough for the index to be used).  The final query was

   with x as
        (select p.publisher as publisher,
                (select max(t.timestamp) as timestamp from table as t
                        where t.publisher = p.publisher)
                from publisher as p)
        select publisher, extract (epoch from (now() - timestamp)) from x;

to give the number of seconds since the latest timestamp.

Andrew