Re: Slow join

Roman Kushnir wrote:
> The following basic inner join is taking too much time for me. (I’m using count(videos.id)
> instead of count(*) because my actual query looks different, but I simplified it here to the essence).
> I’ve tried following random people's suggestions and adjusting the random_page_cost
> (decreasing it from 4 to 1.1) without a stable improvement. Any hints on what is wrong here? Thank you.
>
> The query
>
> SELECT COUNT(videos.id) FROM videos JOIN accounts ON accounts.channel_id = videos.channel_id;
>
> The accounts table has 744 rows, videos table has 2.2M rows, the join produces 135k rows.
>
> Running on Amazon RDS, with default 10.1 parameters
>
> version
> ---------------------------------------------------------------------------------------------------------
> PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
>
> Execution plan https://explain.depesz.com/s/gf7

Your time is spent here:

> -> Parallel Seq Scan on videos (cost=0.00..480898.90 rows=940390 width=16) (actual time=0.687..55555.774 rows=764042 loops=3)
> Buffers: shared hit=7138 read=464357

55 seconds to scan 3.5 GB is not so bad.

What I wonder is how it is that you have less than two rows per table block.
Could it be that the table is very bloated?

If you can, you could "VACUUM (FULL) videos" and see if that makes a difference.
If you can bring the table size down, it will speed up query performance.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com