Re: Slow join

Hi Justin,

Thank you for your comments.

As you mentioned the size of shared buffers, my first thought was to just switch to a larger machine as this one only has 2 gigs of RAM. But then it occurred to me that the whole videos table is getting loaded into memory while only 2 small columns are actually used! So I created a covering index on videos (channel_id, id) and the query now completes in 190ms!

Thanks, you helped me a lot.

> On Jun 25, 2018, at 6:45 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> 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