Date: Mon, 25 Jun 2018 11:45:22 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Roman Kushnir <roman.kushnir@ad2games.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow join
Message-ID: <20180625164522.GE20857@telsasoft.com>
References: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <0B4D190F-CC48-4763-918D-4D4A8DAE5B0B@ad2games.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

Hi,

Thanks for providing all this info :)

On Mon, Jun 25, 2018 at 05:55:49PM +0200, Roman Kushnir wrote:
> Hello,
> 
> The following basic inner join is taking too much time for me. (I’m using count(videos.id <http://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.

> Running on Amazon RDS, with default 10.1 parameters

All default ?
https://wiki.postgresql.org/wiki/Server_Configuration

It looks like nearly the entire time is spent reading this table:

	Parallel Seq Scan on videos ... (ACTUAL TIME=0.687..55,555.774...)
	Buffers: shared hit=7138 read=464357

Perhaps shared_buffers should be at least several times larger, and perhaps up
to 4gb to keep the entire table in RAM.  You could maybe also benefit from
better device readahead (blockdev --setra or lvchange -r or
/sys/block/sd?/queue/read_ahead_kb)

Also, it looks like there's a row count misestimate, which probably doesn't
matter for the query you sent, but maybe affects your larger query:
	Hash Join (... ROWS=365,328 ... ) (... ROWS=45,307 ... )

If that matters, maybe it'd help to increase statistics on channel_id.
Actually, I see both tables have FK into channels.id:

>     "fk_rails_11d6d9bea2" FOREIGN KEY (channel_id) REFERENCES channels(id)
>     "fk_rails_257f68ae55" FOREIGN KEY (channel_id) REFERENCES channels(id)

I don't see the definition of "channels" (and it looks like the query I put on
the wiki doesn't show null_frac), but I think that postgres since 9.6 should be
able to infer good join statistics from the existence of the FKs.  Maybe that
only works if you actually JOIN to the channels table (?).  But if anything
that's only a 2ndary problem, if at all.

Justin