From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Roman Kushnir <roman.kushnir@ad2games.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow join
Date: Wed, 27 Jun 2018 10:19:18 +0200
Message-ID: <1530087558.2417.30.camel@cybertec.at> (raw)
In-Reply-To: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
References: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
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: laurenz.albe@cybertec.at, roman.kushnir@ad2games.com
Subject: Re: Slow join
In-Reply-To: <1530087558.2417.30.camel@cybertec.at>
* 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