Hi Laurenz, You’re right about the table being bloated, the videos.description column is large. I thought about moving it to a separate table, but having an index only on the columns used in the query seems to have compensated for that already. Thank you. > On Jun 27, 2018, at 10:19 AM, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > > 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
