Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 11.4 \(3445.8.2\))
Subject: Re: Slow join
From: Roman Kushnir <roman.kushnir@ad2games.com>
In-Reply-To: <1530087558.2417.30.camel@cybertec.at>
Date: Wed, 27 Jun 2018 11:02:38 +0200
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <5428CDF1-725B-4539-B569-4FD661664449@ad2games.com>
References: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
 <1530087558.2417.30.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Precedence: bulk

Hi Laurenz,

You=E2=80=99re 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:
>=20
> Roman Kushnir wrote:
>> The following basic inner join is taking too much time for me. (I=E2=80=
=99m using count(videos.id)
>> instead of count(*) because my actual query looks different, but I =
simplified it here to the essence).
>> I=E2=80=99ve 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.
>>=20
>> The query
>>=20
>> SELECT COUNT(videos.id) FROM videos JOIN accounts ON =
accounts.channel_id =3D videos.channel_id;
>>=20
>> The accounts table has 744 rows, videos table has 2.2M rows, the join =
produces 135k rows.
>>=20
>> Running on Amazon RDS, with default 10.1 parameters
>>=20
>>                                                 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
>>=20
>> Execution plan https://explain.depesz.com/s/gf7
>=20
> Your time is spent here:
>=20
>> ->  Parallel Seq Scan on videos  (cost=3D0.00..480898.90 rows=3D940390 =
width=3D16) (actual time=3D0.687..55555.774 rows=3D764042 loops=3D3)
>>      Buffers: shared hit=3D7138 read=3D464357
>=20
> 55 seconds to scan 3.5 GB is not so bad.
>=20
> 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?
>=20
> 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.
>=20
> Yours,
> Laurenz Albe
> --=20
> Cybertec | https://www.cybertec-postgresql.com