From: Roman Kushnir <roman.kushnir@ad2games.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow join
Date: Wed, 27 Jun 2018 11:02:38 +0200
Message-ID: <5428CDF1-725B-4539-B569-4FD661664449@ad2games.com> (raw)
In-Reply-To: <1530087558.2417.30.camel@cybertec.at>
References: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
<1530087558.2417.30.camel@cybertec.at>
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@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=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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: roman.kushnir@ad2games.com, laurenz.albe@cybertec.at
Subject: Re: Slow join
In-Reply-To: <5428CDF1-725B-4539-B569-4FD661664449@ad2games.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox