Slow join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

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


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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux