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
