0

I have two tables, a (id, timestamp) and b (id, value).

They have equivalent number of rows (in fact b is materialized view of a and several other tables). There are around 1.3m rows on each table. I am trying to do a simple join, filter, and group by :

explain select b.value, count(*)
from b
         join "a" on "a"."id" = "a"."id"
where ("a"."timestamp" < timestamp with time zone '2022-08-11 23:59:59.999999999+02:00' and
       "a"."timestamp" >= timestamp with time zone '2022-07-11 00:00:00+02:00')
group by b.value
order by b.value

I have created a few indexes:

create index a_id_timestamp on a ((id::text), timestamp);
create index a_timestamp_id on a (timestamp, (id::text));
create index b_id on b (id);

But it takes very long to compute (>10min). Here is the query plan

Finalize GroupAggregate  (cost=10370525812.24..10370525815.53 rows=13 width=12)
  Group Key: b.value
  ->  Gather Merge  (cost=10370525812.24..10370525815.27 rows=26 width=12)
        Workers Planned: 2
        ->  Sort  (cost=10370524812.21..10370524812.25 rows=13 width=12)
              Sort Key: b.value
              ->  Partial HashAggregate  (cost=10370524811.84..10370524811.97 rows=13 width=12)
                    Group Key: b.value
                    ->  Nested Loop  (cost=0.00..8717589054.95 rows=330587151378 width=4)
                          ->  Parallel Seq Scan on conversations  (cost=0.00..24570.51 rows=244181 width=0)
"                                Filter: (((id)::text IS NOT NULL) AND (""timestamp"" < '2022-08-11 22:00:00+00'::timestamp with time zone) AND (""timestamp"" >= '2022-07-10 22:00:00+00'::timestamp with time zone))"
                          ->  Seq Scan on b  (cost=0.00..22162.62 rows=1353862 width=4)
0

1 Answer 1

1

from b join "a" on "a"."id" = "a"."id"

Is this just a typo, or could it be the source of your problem?

4
  • 1
    Shouldn't this be a comment? Maybe?
    – Vérace
    Commented Aug 11, 2022 at 10:58
  • @Vérace-СлаваУкраїні, no I would say it is the answer. The typo is the cause of the problem.
    – jjanes
    Commented Aug 11, 2022 at 16:28
  • @jjanes - hmmm... I did this - I can see the point - .5s vs 2.4ms! Thanks for provoking that thought! A glance at the result should have given the OP some sort of idea...
    – Vérace
    Commented Aug 11, 2022 at 19:47
  • 1
    Agreed. As written, it's a Cartesian Join (every record in one table joined to every record in the other table). If the O.P. were working with tables of any reasonable size, the slow down in query speed and size of the resulting (i.e. huge!) result set would be a fairly good indication that something was badly wrong!
    – Phill W.
    Commented Aug 12, 2022 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.