Hi Laurenz,
You’re right about the table being bloated, the videos.description column is large. I thought about moving it to a
separatetable, 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@cybertec.at> 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=764042loops=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
