Обсуждение: Slow join
Hello,
Running on Amazon RDS, with default 10.1 parameters
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
Structure and statistics of the tables involved
=> \d videos
Table "public.videos"
Column | Type | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------------------------------------------------
id | bigint | | not null | nextval('videos_id_seq'::regclass)
vendor_id | character varying | | not null |
channel_id | bigint | | |
published_at | timestamp without time zone | | |
title | text | | |
description | text | | |
thumbnails | jsonb | | |
tags | character varying[] | | |
category_id | character varying | | |
default_language | character varying | | |
default_audio_language | character varying | | |
duration | integer | | |
stereoscopic | boolean | | |
hd | boolean | | |
captioned | boolean | | |
licensed | boolean | | |
projection | character varying | | |
privacy_status | character varying | | |
license | character varying | | |
embeddable | boolean | | |
terminated_at | timestamp without time zone | | |
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
featured_game_id | bigint | | |
Indexes:
"videos_pkey" PRIMARY KEY, btree (id)
"index_videos_on_vendor_id" UNIQUE, btree (vendor_id)
"index_videos_on_channel_id" btree (channel_id)
"index_videos_on_featured_game_id" btree (featured_game_id)
Foreign-key constraints:
"fk_rails_257f68ae55" FOREIGN KEY (channel_id) REFERENCES channels(id)
"fk_rails_ce1b3e10b0" FOREIGN KEY (featured_game_id) REFERENCES games(id)
Referenced by:
TABLE "video_fetch_statuses" CONSTRAINT "fk_rails_3bfdf013b8" FOREIGN KEY (video_id) REFERENCES videos(id)
TABLE "video_daily_facts" CONSTRAINT "fk_rails_dc0eca9ebb" FOREIGN KEY (video_id) REFERENCES videos(id)
=> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='videos’;
relname | relpages | reltuples | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
-----------------------+----------+-------------+---------------+---------+----------+----------------+------------+---------------
videos | 471495 | 2.25694e+06 | 471389 | r | 24 | f | | 4447764480
=> SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='channel_id' AND tablename='videos' ORDER BY 1 DESC;
frac_mcv | tablename | attname | n_distinct | n_mcv | n_hist
----------+-----------------------+----------------+------------+-------+--------
0.1704 | videos | channel_id | 1915 | 100 | 101
=> \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+--------------------------------------------------
id | bigint | | not null | nextval('accounts_id_seq'::regclass)
channel_id | bigint | | not null |
refresh_token | character varying | | not null |
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"index_accounts_on_channel_id" UNIQUE, btree (channel_id)
"index_accounts_on_refresh_token" UNIQUE, btree (refresh_token)
Foreign-key constraints:
"fk_rails_11d6d9bea2" FOREIGN KEY (channel_id) REFERENCES channels(id)
=> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='accounts’;
relname | relpages | reltuples | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
----------------------+----------+-----------+---------------+---------+----------+----------------+------------+---------------
accounts | 23 | 744 | 23 | r | 5 | f | | 229376
=> SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='channel_id' AND tablename='accounts' ORDER BY 1 DESC;
frac_mcv | tablename | attname | n_distinct | n_mcv | n_hist
----------+----------------------+----------------+------------+-------+--------
| accounts | channel_id | -1 | | 101
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/>) insteadof 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) withouta 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
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@telsasoft.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
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=764042loops=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
Hi Laurenz, You’re right about the table being bloated, the videos.description column is large. I thought about moving it to a separatetable, 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=764042loops=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