ReSQL.ru
     
powered by simpleCommunicator - 2.0.54     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Медленное планирование запроса
8 сообщений из 8, страница 1 из 1
Медленное планирование запроса
    #39381513
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Столкнулся с проблемой. Слишком много времени, полсекунды, постгрес тратит на планирование запроса. План выбирает правильный, к этому претензий нет. Исполняется запрос тоже быстро, за сотые доли секунды. На сервере Postgres 9.5.
 
запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
EXPLAIN
    (ANALYZE)
SELECT
    *
FROM
    "usage"
    INNER JOIN "time" ON ("usage"."time_id" = "time"."id")
    INNER JOIN "geo" ON ("usage"."geo_id" = "geo"."id")
    INNER JOIN "clienturi" ON ("usage"."clienturi_id" = "clienturi"."id")
    INNER JOIN "useragent" ON ("usage"."useragent_id" = "useragent"."id")
    INNER JOIN "backend" ON ("usage"."backend_id" = "backend"."id")
    INNER JOIN "proto" ON ("usage"."proto_id" = "proto"."id")
    INNER JOIN "referrer" ON ("usage"."referrer_id" = "referrer"."id")
    INNER JOIN "clienturi" T9 ON ("usage"."clienturi_parent_id" = T9."id")
WHERE
    "time"."year" IS NOT NULL AND "time"."month" IS NULL AND "time"."day" IS NULL AND "time"."hour" IS NULL AND "time"."minute" IS NULL
    AND "usage"."geo_id" IN (1, 753)
    AND "usage"."clienturi_id" IN (288119418, 559795958)
    AND "usage"."useragent_id" IN (1, 4176)
    AND "usage"."backend_id" IN (86, 210)
    AND "usage"."proto_id" IN (1, 68)
    AND "usage"."referrer_id" IN (1, 11713775)
ORDER BY
    "time"."year" ASC;

 
план
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.85..25312874517804736.00 rows=4220849214413699809280 width=399) (actual time=7.131..22.270 rows=4 loops=1)
   ->  Nested Loop  (cost=0.85..8895890141.26 rows=1482451866583578 width=310) (actual time=7.122..22.233 rows=4 loops=1)
         ->  Nested Loop  (cost=0.85..150152.76 rows=520980380 width=221) (actual time=7.109..22.194 rows=4 loops=1)
               ->  Nested Loop  (cost=0.85..28358.34 rows=7194 width=195) (actual time=7.101..22.157 rows=4 loops=1)
                     ->  Nested Loop  (cost=0.85..23782.28 rows=313 width=164) (actual time=7.094..22.129 rows=4 loops=1)
                           ->  Nested Loop  (cost=0.85..23438.48 rows=46 width=130) (actual time=7.083..22.088 rows=4 loops=1)
                                 ->  Nested Loop  (cost=0.85..23372.31 rows=5 width=121) (actual time=7.076..22.061 rows=4 loops=1)
                                       ->  Nested Loop  (cost=0.85..23358.88 rows=3 width=115) (actual time=7.070..22.037 rows=4 loops=1)
                                             ->  Merge Append  (cost=0.85..15693.00 rows=3 width=28) (actual time=1.269..15.570 rows=5 loops=1)
                                                   Sort Key: "time".year
                                                   ->  Index Scan using time_year_month_day_hour_minute_second_idx on "time"  (cost=0.12..8.15 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)
                                                         Index Cond: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                                   ->  Index Scan using time_2016_year_month_day_hour_minute_second_idx on time_2016  (cost=0.42..15549.80 rows=1 width=28) (actual time=1.128..15.427 rows=4 loops=1)
                                                         Index Cond: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                                   ->  Index Scan using time_2017_year_month_day_hour_minute_second_idx on time_2017  (cost=0.28..134.97 rows=1 width=28) (actual time=0.131..0.132 rows=1 loops=1)
                                                         Index Cond: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                             ->  Append  (cost=0.00..2555.10 rows=20 width=87) (actual time=0.491..1.291 rows=1 loops=5)
                                                   ->  Seq Scan on usage  (cost=0.00..0.00 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])) AND ("time".id = time_id))
                                                   ->  Index Scan using usage_2016_time_id_clienturi_id_geo_id_useragent_id_backen_idx on usage_2016  (cost=0.70..306.57 rows=1 width=87) (actual time=0.071..0.183 rows=1 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2016_08_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_08  (cost=0.71..306.73 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2016_09_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_09  (cost=0.71..306.73 rows=1 width=87) (actual time=0.155..0.155 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2016_10_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_10  (cost=0.71..306.89 rows=1 width=87) (actual time=0.153..0.153 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2016_11_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_11  (cost=0.71..306.89 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2016_12_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_12  (cost=0.71..306.89 rows=1 width=87) (actual time=0.153..0.153 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2017_time_id_clienturi_id_geo_id_useragent_id_backend_idx on usage_2017  (cost=0.56..297.45 rows=1 width=87) (actual time=0.142..0.142 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Index Scan using usage_2017_01_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2017_01  (cost=0.70..306.57 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                                                         Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                   ->  Bitmap Heap Scan on usage_2017_02  (cost=1.51..10.03 rows=1 width=87) (actual time=0.002..0.002 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::in
teger[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_02_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_03  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_03_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_04  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_04_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_05  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_05_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_06  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_06_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_07  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_07_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_08  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_08_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_09  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_09_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_10  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_10_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_11  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_11_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                                   ->  Bitmap Heap Scan on usage_2017_12  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                         Recheck Cond: (time_id = "time".id)
                                                         Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Index Scan on usage_2017_12_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Index Cond: (time_id = "time".id)
                                       ->  Append  (cost=0.00..4.45 rows=3 width=6) (actual time=0.004..0.005 rows=1 loops=4)
                                             ->  Seq Scan on backend  (cost=0.00..0.00 rows=1 width=6) (actual time=0.000..0.000 rows=0 loops=4)
                                                   Filter: (usage.backend_id = id)
                                             ->  Index Scan using backend_2016_pkey on backend_2016  (cost=0.14..2.22 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=4)
                                                   Index Cond: (id = usage.backend_id)
                                             ->  Index Scan using backend_2017_pkey on backend_2017  (cost=0.14..2.22 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=4)
                                                   Index Cond: (id = usage.backend_id)
                                 ->  Append  (cost=0.00..13.20 rows=3 width=28) (actual time=0.004..0.006 rows=1 loops=4)
                                       ->  Seq Scan on geo  (cost=0.00..0.00 rows=1 width=66) (actual time=0.000..0.000 rows=0 loops=4)
                                             Filter: (usage.geo_id = id)
                                       ->  Index Scan using geo_2016_pkey on geo_2016  (cost=0.28..6.54 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=4)
                                             Index Cond: (id = usage.geo_id)
                                       ->  Index Scan using geo_2017_pkey on geo_2017  (cost=0.28..6.66 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=4)
                                             Index Cond: (id = usage.geo_id)
                           ->  Append  (cost=0.00..7.44 rows=3 width=34) (actual time=0.006..0.009 rows=1 loops=4)
                                 ->  Seq Scan on proto  (cost=0.00..0.00 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=4)
                                       Filter: (usage.proto_id = id)
                                 ->  Seq Scan on proto_2016  (cost=0.00..1.61 rows=1 width=34) (actual time=0.004..0.007 rows=1 loops=4)
                                       Filter: (usage.proto_id = id)
                                       Rows Removed by Filter: 48
                                 ->  Index Scan using proto_2017_pkey on proto_2017  (cost=0.15..5.83 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=4)
                                       Index Cond: (id = usage.proto_id)
                     ->  Append  (cost=0.00..14.59 rows=3 width=40) (actual time=0.004..0.006 rows=1 loops=4)
                           ->  Seq Scan on useragent  (cost=0.00..0.00 rows=1 width=66) (actual time=0.000..0.000 rows=0 loops=4)
                                 Filter: (usage.useragent_id = id)
                           ->  Index Scan using useragent_2016_pkey on useragent_2016  (cost=0.28..7.93 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=4)
                                 Index Cond: (id = usage.useragent_id)
                           ->  Index Scan using useragent_2017_pkey on useragent_2017  (cost=0.27..6.66 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=4)
                                 Index Cond: (id = usage.useragent_id)
               ->  Append  (cost=0.00..16.90 rows=3 width=29) (actual time=0.005..0.008 rows=1 loops=4)
                     ->  Seq Scan on referrer  (cost=0.00..0.00 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=4)
                           Filter: (usage.referrer_id = id)
                     ->  Index Scan using referrer_2016_pkey on referrer_2016  (cost=0.43..8.45 rows=1 width=26) (actual time=0.004..0.004 rows=1 loops=4)
                           Index Cond: (id = usage.referrer_id)
                     ->  Index Scan using referrer_2017_pkey on referrer_2017  (cost=0.43..8.45 rows=1 width=25) (actual time=0.003..0.003 rows=0 loops=4)
                           Index Cond: (id = usage.referrer_id)
         ->  Append  (cost=0.00..17.05 rows=3 width=86) (actual time=0.008..0.009 rows=1 loops=4)
               ->  Seq Scan on clienturi  (cost=0.00..0.00 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=4)
                     Filter: (usage.clienturi_id = id)
               ->  Index Scan using clienturi_2016_pkey1 on clienturi_2016  (cost=0.57..8.59 rows=1 width=89) (actual time=0.006..0.006 rows=1 loops=4)
                     Index Cond: (id = usage.clienturi_id)
               ->  Index Scan using clienturi_2017_pkey on clienturi_2017  (cost=0.43..8.45 rows=1 width=85) (actual time=0.002..0.002 rows=0 loops=4)
                     Index Cond: (id = usage.clienturi_id)
   ->  Append  (cost=0.00..17.05 rows=3 width=86) (actual time=0.004..0.008 rows=1 loops=4)
         ->  Seq Scan on clienturi t9  (cost=0.00..0.00 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=4)
               Filter: (usage.clienturi_parent_id = id)
         ->  Index Scan using clienturi_2016_pkey1 on clienturi_2016 t9_1  (cost=0.57..8.59 rows=1 width=89) (actual time=0.004..0.005 rows=1 loops=4)
               Index Cond: (id = usage.clienturi_parent_id)
         ->  Index Scan using clienturi_2017_pkey on clienturi_2017 t9_2  (cost=0.43..8.45 rows=1 width=85) (actual time=0.003..0.003 rows=0 loops=4)
               Index Cond: (id = usage.clienturi_parent_id)
 Planning time: 429.148 ms
 Execution time: 22.804 ms

Сделал тест, на котором вопроизводится эта проблема. Пробовал на Postgres 9.6.
 
тест
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
rollback;
begin;

create table dim1 (id serial primary key);
create table dim2 (id serial primary key);
create table dim3 (id serial primary key);
create table dim4 (id serial primary key);
create table dim5 (id serial primary key);
create table dim6 (id serial primary key);

create table usage (
    id serial primary key,
    dim1_id int not null,
    dim2_id int not null,
    dim3_id int not null,
    dim4_id int not null,
    dim5_id int not null,
    dim6_id int not null
);
create index on usage (dim1_id, dim2_id, dim3_id, dim4_id, dim5_id, dim6_id);
create index on usage (dim2_id, dim3_id, dim4_id, dim5_id, dim6_id, dim1_id);
create index on usage (dim3_id, dim4_id, dim5_id, dim6_id, dim1_id, dim2_id);
create index on usage (dim4_id, dim5_id, dim6_id, dim1_id, dim2_id, dim3_id);
create index on usage (dim5_id, dim6_id, dim1_id, dim2_id, dim3_id, dim4_id);
create index on usage (dim6_id, dim1_id, dim2_id, dim3_id, dim4_id, dim5_id);

create table usage_2017_01(like usage including indexes) inherits(usage);
create table usage_2017_02(like usage including indexes) inherits(usage);
create table usage_2017_03(like usage including indexes) inherits(usage);
create table usage_2017_04(like usage including indexes) inherits(usage);
create table usage_2017_05(like usage including indexes) inherits(usage);
create table usage_2017_06(like usage including indexes) inherits(usage);
create table usage_2017_07(like usage including indexes) inherits(usage);
create table usage_2017_08(like usage including indexes) inherits(usage);
create table usage_2017_09(like usage including indexes) inherits(usage);
create table usage_2017_10(like usage including indexes) inherits(usage);
create table usage_2017_11(like usage including indexes) inherits(usage);
create table usage_2017_12(like usage including indexes) inherits(usage);
create table usage_2018_01(like usage including indexes) inherits(usage);
create table usage_2018_02(like usage including indexes) inherits(usage);
create table usage_2018_03(like usage including indexes) inherits(usage);
create table usage_2018_04(like usage including indexes) inherits(usage);
create table usage_2018_05(like usage including indexes) inherits(usage);
create table usage_2018_06(like usage including indexes) inherits(usage);
create table usage_2018_07(like usage including indexes) inherits(usage);
create table usage_2018_08(like usage including indexes) inherits(usage);
create table usage_2018_09(like usage including indexes) inherits(usage);
create table usage_2018_10(like usage including indexes) inherits(usage);
create table usage_2018_11(like usage including indexes) inherits(usage);
create table usage_2018_12(like usage including indexes) inherits(usage);

explain
    (analyze)
select
    *
from
    usage
    inner join dim1 on (usage.dim1_id = dim1.id)
    inner join dim2 on (usage.dim2_id = dim2.id)
    inner join dim3 on (usage.dim3_id = dim3.id)
    inner join dim4 on (usage.dim4_id = dim4.id)
    inner join dim5 on (usage.dim5_id = dim5.id)
    inner join dim6 on (usage.dim6_id = dim6.id)
where
    usage.dim1_id in (1, 2) and
    usage.dim2_id in (2, 3) and
    usage.dim3_id in (3, 4) and
    usage.dim4_id in (4, 5) and
    usage.dim5_id in (5, 6) and
    usage.dim6_id in (6, 7)
;

-- rollback;

 
план
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
                                                                                                                                               QUERY PLAN                                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.93..605.67 rows=25 width=52) (actual time=0.089..0.089 rows=0 loops=1)
   ->  Nested Loop  (cost=0.78..557.10 rows=25 width=48) (actual time=0.089..0.089 rows=0 loops=1)
         ->  Nested Loop  (cost=0.62..508.54 rows=25 width=44) (actual time=0.089..0.089 rows=0 loops=1)
               ->  Nested Loop  (cost=0.46..459.98 rows=25 width=40) (actual time=0.089..0.089 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.31..411.42 rows=25 width=36) (actual time=0.089..0.089 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.15..362.85 rows=25 width=32) (actual time=0.087..0.087 rows=0 loops=1)
                                 ->  Append  (cost=0.00..314.29 rows=25 width=28) (actual time=0.087..0.087 rows=0 loops=1)
                                       ->  Seq Scan on usage  (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Filter: ((dim1_id = ANY ('{1,2}'::integer[])) AND (dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim4_id = ANY ('{4,5}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                       ->  Bitmap Heap Scan on usage_2017_01  (cost=9.07..13.10 rows=1 width=28) (actual time=0.009..0.009 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_01_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_01_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_02  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_02_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_02_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_03  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_03_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_03_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_04  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_04_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_04_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_05  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_05_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_05_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_06  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_06_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_06_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_07  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_07_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_07_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_08  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_08_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_08_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_09  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_09_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_09_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_10  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_10_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_10_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_11  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_11_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_11_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2017_12  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2017_12_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2017_12_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_01  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_01_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_01_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_02  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_02_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_02_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_03  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_03_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_03_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_04  (cost=9.07..13.10 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_04_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_04_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_05  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_05_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_05_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_06  (cost=9.07..13.10 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_06_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_06_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_07  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_07_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_07_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_08  (cost=9.07..13.10 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_08_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_08_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_09  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_09_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_09_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_10  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_10_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_10_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_11  (cost=9.07..13.10 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_11_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_11_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                       ->  Bitmap Heap Scan on usage_2018_12  (cost=9.07..13.10 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=1)
                                             Recheck Cond: ((dim4_id = ANY ('{4,5}'::integer[])) AND (dim1_id = ANY ('{1,2}'::integer[])))
                                             Filter: ((dim2_id = ANY ('{2,3}'::integer[])) AND (dim3_id = ANY ('{3,4}'::integer[])) AND (dim5_id = ANY ('{5,6}'::integer[])) AND (dim6_id = ANY ('{6,7}'::integer[])))
                                             ->  BitmapAnd  (cost=9.07..9.07 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Bitmap Index Scan on usage_2018_12_dim4_id_dim5_id_dim6_id_dim1_id_dim2_id_dim3__idx  (cost=0.00..4.41 rows=14 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                         Index Cond: (dim4_id = ANY ('{4,5}'::integer[]))
                                                   ->  Bitmap Index Scan on usage_2018_12_dim1_id_dim2_id_dim3_id_dim4_id_dim5_id_dim6__idx  (cost=0.00..4.41 rows=14 width=0) (never executed)
                                                         Index Cond: (dim1_id = ANY ('{1,2}'::integer[]))
                                 ->  Index Only Scan using dim1_pkey on dim1  (cost=0.15..1.93 rows=1 width=4) (never executed)
                                       Index Cond: (id = usage.dim1_id)
                                       Heap Fetches: 0
                           ->  Index Only Scan using dim2_pkey on dim2  (cost=0.15..1.93 rows=1 width=4) (never executed)
                                 Index Cond: (id = usage.dim2_id)
                                 Heap Fetches: 0
                     ->  Index Only Scan using dim3_pkey on dim3  (cost=0.15..1.93 rows=1 width=4) (never executed)
                           Index Cond: (id = usage.dim3_id)
                           Heap Fetches: 0
               ->  Index Only Scan using dim4_pkey on dim4  (cost=0.15..1.93 rows=1 width=4) (never executed)
                     Index Cond: (id = usage.dim4_id)
                     Heap Fetches: 0
         ->  Index Only Scan using dim5_pkey on dim5  (cost=0.15..1.93 rows=1 width=4) (never executed)
               Index Cond: (id = usage.dim5_id)
               Heap Fetches: 0
   ->  Index Only Scan using dim6_pkey on dim6  (cost=0.15..1.93 rows=1 width=4) (never executed)
         Index Cond: (id = usage.dim6_id)
         Heap Fetches: 0
 Planning time: 2017.584 ms
 Execution time: 1.048 ms

Пробовал крутить настройки geqo, from_collapse_limit, join_collapse_limit, переформулировать запрос. Не помогает.

Как можно ускорить планирование этого запроса?

Не хотелось бы использовать prepared statements, потому что работаем через Django ORM.

Поможет ли здесь pg_pathman? Но check contraints в партиционировании не используем.

PS: При изменении условий теста замедление исчезает полностью или сокращается. Подробности ниже.

 
измененияисходный тест: Planning time: 2017.584 ms Execution time: 1.048 ms

без join dim*: Planning time: 14.284 ms Execution time: 1.893 ms

без индексов: Planning time: 18.578 ms Execution time: 0.447 ms

одна партиция: Planning time: 87.685 ms Execution time: 0.193 ms

без where: Planning time: 411.323 ms Execution time: 0.263 ms
...
Рейтинг: 0 / 0
10.01.2017, 14:24
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381530
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
LeXa NalBat, ради интереса что покажет:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
EXPLAIN
    (ANALYZE)
SELECT
    *
FROM
    "usage"
    INNER JOIN LATERAL (select time.* from "time" where ("usage"."time_id" = "time"."id") limit 1)  time ON TRUE
    INNER JOIN LATERAL (select geo.* from "geo" where ("usage"."geo_id" = "geo"."id") limit 1)  geo ON TRUE
    INNER JOIN LATERAL (select clienturi.* from "clienturi" where ("usage"."clienturi_id" = "clienturi"."id") limit 1)  clienturi ON TRUE
    INNER JOIN LATERAL (select useragent.* from "useragent" where ("usage"."useragent_id" = "useragent"."id") limit 1)  useragent ON TRUE
    INNER JOIN LATERAL (select backend.* from "backend" where ("usage"."backend_id" = "backend"."id") limit 1)  backend ON TRUE
    INNER JOIN LATERAL (select proto.* from "proto" where ("usage"."proto_id" = "proto"."id") limit 1)  proto ON TRUE
    INNER JOIN LATERAL (select referrer.* from "referrer" where ("usage"."referrer_id" = "referrer"."id") limit 1)  referrer ON TRUE
    INNER JOIN LATERAL (select clienturi.* from "clienturi" T9 where ("usage"."clienturi_parent_id" = T9."id") limit 1)  T9 ON TRUE
WHERE
    "time"."year" IS NOT NULL AND "time"."month" IS NULL AND "time"."day" IS NULL AND "time"."hour" IS NULL AND "time"."minute" IS NULL
    AND "usage"."geo_id" IN (1, 753)
    AND "usage"."clienturi_id" IN (288119418, 559795958)
    AND "usage"."useragent_id" IN (1, 4176)
    AND "usage"."backend_id" IN (86, 210)
    AND "usage"."proto_id" IN (1, 68)
    AND "usage"."referrer_id" IN (1, 11713775)
ORDER BY
    "time"."year" ASC;

?
...
Рейтинг: 0 / 0
10.01.2017, 14:43
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381539
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
LeXa NalBat,

Партиционирование, однако.

Насчёт pathman-а -- он может помочь, ибо ради этого его и делали. Но:
после анонса я спрашивал, можно ли его прикрутить к существующим партициям. Было нельзя, как сейчас -- не знаю.

был баг, который отдавал неверные данные (в фейсбуке пробегал). надо убедиться, что он закрыт. Не знаю, есть ли у проекта трэкер какой.
...
Рейтинг: 0 / 0
10.01.2017, 14:49
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381598
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Legushka,

Да, классно, планирование чуть измененного вашего запроса работает быстро. (Нужно сначала выбрать из time, затем из usage, затем из остальных.)
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
EXPLAIN
    (ANALYZE)
SELECT
    *
FROM
    "usage"
    INNER JOIN "time" ON ("usage"."time_id" = "time"."id")
    INNER JOIN LATERAL (select geo.* from "geo" where ("usage"."geo_id" = "geo"."id") limit 1)  geo ON TRUE
    INNER JOIN LATERAL (select clienturi.* from "clienturi" where ("usage"."clienturi_id" = "clienturi"."id") limit 1)  clienturi ON TRUE
    INNER JOIN LATERAL (select useragent.* from "useragent" where ("usage"."useragent_id" = "useragent"."id") limit 1)  useragent ON TRUE
    INNER JOIN LATERAL (select backend.* from "backend" where ("usage"."backend_id" = "backend"."id") limit 1)  backend ON TRUE
    INNER JOIN LATERAL (select proto.* from "proto" where ("usage"."proto_id" = "proto"."id") limit 1)  proto ON TRUE
    INNER JOIN LATERAL (select referrer.* from "referrer" where ("usage"."referrer_id" = "referrer"."id") limit 1)  referrer ON TRUE
    INNER JOIN LATERAL (select clienturi.* from "clienturi" T9 where ("usage"."clienturi_parent_id" = T9."id") limit 1)  T9 ON TRUE
WHERE
    "time"."year" IS NOT NULL AND "time"."month" IS NULL AND "time"."day" IS NULL AND "time"."hour" IS NULL AND "time"."minute" IS NULL
    AND "usage"."geo_id" IN (1, 753)
    AND "usage"."clienturi_id" IN (288119418, 559795958)
    AND "usage"."useragent_id" IN (1, 4176)
    AND "usage"."backend_id" IN (86, 210)
    AND "usage"."proto_id" IN (1, 68)
    AND "usage"."referrer_id" IN (1, 11713775)
ORDER BY
    "time"."year" ASC;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
                                                                                                                                                                                               QUERY PLAN                                                                                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=14080.84..14080.85 rows=3 width=422) (actual time=40.767..40.767 rows=4 loops=1)
   Sort Key: "time".year
   Sort Method: quicksort  Memory: 26kB
   ->  Nested Loop  (cost=0.00..14080.82 rows=3 width=422) (actual time=3.985..40.757 rows=4 loops=1)
         ->  Nested Loop  (cost=0.00..14063.72 rows=3 width=338) (actual time=3.977..40.727 rows=4 loops=1)
               ->  Nested Loop  (cost=0.00..14046.76 rows=3 width=309) (actual time=3.972..40.700 rows=4 loops=1)
                     ->  Nested Loop  (cost=0.00..14036.91 rows=3 width=275) (actual time=3.968..40.679 rows=4 loops=1)
                           ->  Nested Loop  (cost=0.00..14031.03 rows=3 width=269) (actual time=3.954..40.623 rows=4 loops=1)
                                 ->  Nested Loop  (cost=0.00..14014.38 rows=3 width=229) (actual time=3.951..40.599 rows=4 loops=1)
                                       ->  Nested Loop  (cost=0.00..13997.27 rows=3 width=143) (actual time=3.945..40.573 rows=4 loops=1)
                                             ->  Nested Loop  (cost=0.00..13980.63 rows=3 width=115) (actual time=3.940..40.550 rows=4 loops=1)
                                                   ->  Append  (cost=0.00..6314.74 rows=3 width=28) (actual time=0.007..34.064 rows=5 loops=1)
                                                         ->  Seq Scan on "time"  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                                               Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                                         ->  Seq Scan on time_2016  (cost=0.00..6208.78 rows=1 width=28) (actual time=0.006..33.741 rows=4 loops=1)
                                                               Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                                               Rows Removed by Filter: 371894
                                                         ->  Seq Scan on time_2017  (cost=0.00..105.96 rows=1 width=28) (actual time=0.289..0.322 rows=1 loops=1)
                                                               Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                                                               Rows Removed by Filter: 2971
                                                   ->  Append  (cost=0.00..2555.10 rows=20 width=87) (actual time=0.476..1.295 rows=1 loops=5)
                                                         ->  Seq Scan on usage  (cost=0.00..0.00 rows=1 width=87) (actual time=0.000..0.000 rows=0 loops=5)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])) AND ("time".id = time_id))
                                                         ->  Index Scan using usage_2016_time_id_clienturi_id_geo_id_useragent_id_backen_idx on usage_2016  (cost=0.70..306.57 rows=1 width=87) (actual time=0.069..0.183 rows=1 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2016_08_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_08  (cost=0.71..306.73 rows=1 width=87) (actual time=0.155..0.155 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2016_09_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_09  (cost=0.71..306.73 rows=1 width=87) (actual time=0.156..0.156 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2016_10_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_10  (cost=0.71..306.89 rows=1 width=87) (actual time=0.155..0.155 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2016_11_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_11  (cost=0.71..306.89 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2016_12_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_12  (cost=0.71..306.89 rows=1 width=87) (actual time=0.156..0.156 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2017_time_id_clienturi_id_geo_id_useragent_id_backend_idx on usage_2017  (cost=0.56..297.45 rows=1 width=87) (actual time=0.144..0.144 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Index Scan using usage_2017_01_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2017_01  (cost=0.70..306.57 rows=1 width=87) (actual time=0.155..0.155 rows=0 loops=5)
                                                               Index Cond: ((time_id = "time".id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                         ->  Bitmap Heap Scan on usage_2017_02  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_02_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_03  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_03_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_04  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_04_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_05  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_05_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_06  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_06_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_07  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_07_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_08  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_08_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_09  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_09_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_10  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_10_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_11  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_11_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                                         ->  Bitmap Heap Scan on usage_2017_12  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                                                               Recheck Cond: (time_id = "time".id)
                                                               Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                                                               ->  Bitmap Index Scan on usage_2017_12_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                                                                     Index Cond: (time_id = "time".id)
                                             ->  Limit  (cost=0.00..5.53 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=4)
                                                   ->  Append  (cost=0.00..16.59 rows=3 width=28) (actual time=0.004..0.004 rows=1 loops=4)
                                                         ->  Seq Scan on geo  (cost=0.00..0.00 rows=1 width=66) (actual time=0.000..0.000 rows=0 loops=4)
                                                               Filter: (usage.geo_id = id)
                                                         ->  Index Scan using geo_2016_pkey on geo_2016  (cost=0.28..8.29 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=4)
                                                               Index Cond: (usage.geo_id = id)
                                                         ->  Index Scan using geo_2017_pkey on geo_2017  (cost=0.28..8.29 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=1)
                                                               Index Cond: (usage.geo_id = id)
                                       ->  Limit  (cost=0.00..5.68 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=4)
                                             ->  Append  (cost=0.00..17.05 rows=3 width=86) (actual time=0.005..0.005 rows=1 loops=4)
                                                   ->  Seq Scan on clienturi  (cost=0.00..0.00 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=4)
                                                         Filter: (usage.clienturi_id = id)
                                                   ->  Index Scan using clienturi_2016_pkey1 on clienturi_2016  (cost=0.57..8.59 rows=1 width=89) (actual time=0.003..0.003 rows=1 loops=4)
                                                         Index Cond: (usage.clienturi_id = id)
                                                   ->  Index Scan using clienturi_2017_pkey on clienturi_2017  (cost=0.43..8.45 rows=1 width=85) (actual time=0.003..0.003 rows=1 loops=1)
                                                         Index Cond: (usage.clienturi_id = id)
                                 ->  Limit  (cost=0.00..5.53 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=4)
                                       ->  Append  (cost=0.00..16.59 rows=3 width=40) (actual time=0.005..0.005 rows=1 loops=4)
                                             ->  Seq Scan on useragent  (cost=0.00..0.00 rows=1 width=66) (actual time=0.000..0.000 rows=0 loops=4)
                                                   Filter: (usage.useragent_id = id)
                                             ->  Index Scan using useragent_2016_pkey on useragent_2016  (cost=0.28..8.30 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=4)
                                                   Index Cond: (usage.useragent_id = id)
                                             ->  Index Scan using useragent_2017_pkey on useragent_2017  (cost=0.27..8.29 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=1)
                                                   Index Cond: (usage.useragent_id = id)
                           ->  Limit  (cost=0.00..1.94 rows=1 width=6) (actual time=0.013..0.013 rows=1 loops=4)
                                 ->  Append  (cost=0.00..5.83 rows=3 width=6) (actual time=0.013..0.013 rows=1 loops=4)
                                       ->  Seq Scan on backend  (cost=0.00..0.00 rows=1 width=6) (actual time=0.000..0.000 rows=0 loops=4)
                                             Filter: (usage.backend_id = id)
                                       ->  Seq Scan on backend_2016  (cost=0.00..3.38 rows=1 width=6) (actual time=0.011..0.011 rows=1 loops=4)
                                             Filter: (usage.backend_id = id)
                                             Rows Removed by Filter: 112
                                       ->  Seq Scan on backend_2017  (cost=0.00..2.45 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=1)
                                             Filter: (usage.backend_id = id)
                     ->  Limit  (cost=0.00..3.26 rows=1 width=34) (actual time=0.004..0.004 rows=1 loops=4)
                           ->  Append  (cost=0.00..9.78 rows=3 width=34) (actual time=0.004..0.004 rows=1 loops=4)
                                 ->  Seq Scan on proto  (cost=0.00..0.00 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=4)
                                       Filter: (usage.proto_id = id)
                                 ->  Seq Scan on proto_2016  (cost=0.00..1.61 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=4)
                                       Filter: (usage.proto_id = id)
                                       Rows Removed by Filter: 12
                                 ->  Index Scan using proto_2017_pkey on proto_2017  (cost=0.15..8.17 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=1)
                                       Index Cond: (usage.proto_id = id)
               ->  Limit  (cost=0.00..5.63 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=4)
                     ->  Append  (cost=0.00..16.90 rows=3 width=29) (actual time=0.005..0.005 rows=1 loops=4)
                           ->  Seq Scan on referrer  (cost=0.00..0.00 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=4)
                                 Filter: (usage.referrer_id = id)
                           ->  Index Scan using referrer_2016_pkey on referrer_2016  (cost=0.43..8.45 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=4)
                                 Index Cond: (usage.referrer_id = id)
                           ->  Index Scan using referrer_2017_pkey on referrer_2017  (cost=0.43..8.45 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=1)
                                 Index Cond: (usage.referrer_id = id)
         ->  Limit  (cost=0.00..5.68 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=4)
               ->  Result  (cost=0.00..17.05 rows=3 width=0) (actual time=0.006..0.006 rows=1 loops=4)
                     ->  Append  (cost=0.00..17.05 rows=3 width=0) (actual time=0.005..0.005 rows=1 loops=4)
                           ->  Seq Scan on clienturi t9  (cost=0.00..0.00 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=4)
                                 Filter: (usage.clienturi_parent_id = id)
                           ->  Index Only Scan using clienturi_2016_pkey1 on clienturi_2016 t9_1  (cost=0.57..8.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=4)
                                 Index Cond: (id = usage.clienturi_parent_id)
                                 Heap Fetches: 3
                           ->  Index Only Scan using clienturi_2017_pkey on clienturi_2017 t9_2  (cost=0.43..8.45 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
                                 Index Cond: (id = usage.clienturi_parent_id)
                                 Heap Fetches: 1
 Planning time: 6.179 ms
 Execution time: 41.220 ms

Вот еще получилось переформулировать через WITH, чтобы "материализовать" в первоочередной джоин time и usage. (Не сработал с ORDER BY.)
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
EXPLAIN
    (ANALYZE)
WITH "usage" AS (
SELECT
    usage.*
FROM
    "usage"
    INNER JOIN "time" ON ("usage"."time_id" = "time"."id")
WHERE
    "time"."year" IS NOT NULL AND "time"."month" IS NULL AND "time"."day" IS NULL AND "time"."hour" IS NULL AND "time"."minute" IS NULL
    AND "usage"."geo_id" IN (1, 753)
    AND "usage"."clienturi_id" IN (288119418, 559795958)
    AND "usage"."useragent_id" IN (1, 4176)
    AND "usage"."backend_id" IN (86, 210)
    AND "usage"."proto_id" IN (1, 68)
    AND "usage"."referrer_id" IN (1, 11713775)
)
SELECT
    *
FROM
    "usage"
    INNER JOIN "time" ON ("usage"."time_id" = "time"."id")
    INNER JOIN "geo" ON ("usage"."geo_id" = "geo"."id")
    INNER JOIN "clienturi" ON ("usage"."clienturi_id" = "clienturi"."id")
    INNER JOIN "useragent" ON ("usage"."useragent_id" = "useragent"."id")
    INNER JOIN "backend" ON ("usage"."backend_id" = "backend"."id")
    INNER JOIN "proto" ON ("usage"."proto_id" = "proto"."id")
    INNER JOIN "referrer" ON ("usage"."referrer_id" = "referrer"."id")
    INNER JOIN "clienturi" T9 ON ("usage"."clienturi_parent_id" = T9."id")
--ORDER BY
--    "time"."year" ASC

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
                                                                                                                                                                           QUERY PLAN                                                                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=14145.36..47806414351074263040.00 rows=7972925518554397927276544 width=399) (actual time=40.936..41.298 rows=4 loops=1)
   CTE usage
     ->  Nested Loop  (cost=0.00..13980.63 rows=3 width=87) (actual time=4.021..40.659 rows=4 loops=1)
           ->  Append  (cost=0.00..6314.74 rows=3 width=4) (actual time=0.007..34.250 rows=5 loops=1)
                 ->  Seq Scan on "time" time_1  (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)
                       Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                 ->  Seq Scan on time_2016 time_2016_1  (cost=0.00..6208.78 rows=1 width=4) (actual time=0.006..33.907 rows=4 loops=1)
                       Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                       Rows Removed by Filter: 371894
                 ->  Seq Scan on time_2017 time_2017_1  (cost=0.00..105.96 rows=1 width=4) (actual time=0.306..0.342 rows=1 loops=1)
                       Filter: ((year IS NOT NULL) AND (month IS NULL) AND (day IS NULL) AND (hour IS NULL) AND (minute IS NULL))
                       Rows Removed by Filter: 2974
           ->  Append  (cost=0.00..2555.10 rows=20 width=87) (actual time=0.485..1.280 rows=1 loops=5)
                 ->  Seq Scan on usage usage_1  (cost=0.00..0.00 rows=1 width=87) (actual time=0.000..0.000 rows=0 loops=5)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])) AND (time_1.id = time_id))
                 ->  Index Scan using usage_2016_time_id_clienturi_id_geo_id_useragent_id_backen_idx on usage_2016  (cost=0.70..306.57 rows=1 width=87) (actual time=0.072..0.185 rows=1 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2016_08_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_08  (cost=0.71..306.73 rows=1 width=87) (actual time=0.152..0.152 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2016_09_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_09  (cost=0.71..306.73 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2016_10_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_10  (cost=0.71..306.89 rows=1 width=87) (actual time=0.152..0.152 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2016_11_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_11  (cost=0.71..306.89 rows=1 width=87) (actual time=0.150..0.150 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2016_12_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2016_12  (cost=0.71..306.89 rows=1 width=87) (actual time=0.153..0.153 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2017_time_id_clienturi_id_geo_id_useragent_id_backend_idx on usage_2017  (cost=0.56..297.45 rows=1 width=87) (actual time=0.138..0.138 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Index Scan using usage_2017_01_time_id_clienturi_id_geo_id_useragent_id_back_idx on usage_2017_01  (cost=0.70..306.57 rows=1 width=87) (actual time=0.154..0.154 rows=0 loops=5)
                       Index Cond: ((time_id = time_1.id) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (geo_id = ANY ('{1,753}'::integer[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                 ->  Bitmap Heap Scan on usage_2017_02  (cost=1.51..10.03 rows=1 width=87) (actual time=0.002..0.002 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_02_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_03  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_03_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_04  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('
{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_04_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_05  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_05_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_06  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_06_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_07  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_07_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_08  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_08_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_09  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_09_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_10  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_10_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_11  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_11_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
                 ->  Bitmap Heap Scan on usage_2017_12  (cost=1.51..10.03 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=5)
                       Recheck Cond: (time_id = time_1.id)
                       Filter: ((geo_id = ANY ('{1,753}'::integer[])) AND (clienturi_id = ANY ('{288119418,559795958}'::bigint[])) AND (useragent_id = ANY ('{1,4176}'::integer[])) AND (backend_id = ANY ('{86,210}'::integer[])) AND (proto_id = ANY ('{1,68}'::integer[])) AND (referrer_id = ANY ('{1,11713775}'::integer[])))
                       ->  Bitmap Index Scan on usage_2017_12_time_id_clienturi_parent_id_geo_id_useragent__idx  (cost=0.00..1.51 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=5)
                             Index Cond: (time_id = time_1.id)
   ->  Nested Loop  (cost=164.74..16788030723556.76 rows=2799789022725828096 width=310) (actual time=40.930..41.275 rows=4 loops=1)
         ->  Nested Loop  (cost=164.74..229708221.43 rows=983180147311 width=221) (actual time=40.918..41.239 rows=4 loops=1)
               ->  Nested Loop  (cost=164.74..121033.19 rows=13560968 width=195) (actual time=40.911..41.212 rows=4 loops=1)
                     ->  Merge Join  (cost=164.74..407.79 rows=7194 width=167) (actual time=40.903..41.175 rows=4 loops=1)
                           Merge Cond: (geo.id = usage.geo_id)
                           ->  Merge Append  (cost=0.70..132.08 rows=1974 width=9) (actual time=0.021..0.236 rows=753 loops=1)
                                 Sort Key: geo.id
                                 ->  Index Scan using geo_pkey on geo  (cost=0.12..8.14 rows=1 width=66) (actual time=0.002..0.002 rows=0 loops=1)
                                 ->  Index Scan using geo_2016_pkey on geo_2016  (cost=0.28..44.03 rows=747 width=9) (actual time=0.009..0.122 rows=749 loops=1)
                                 ->  Index Scan using geo_2017_pkey on geo_2017  (cost=0.28..43.67 rows=1226 width=9) (actual time=0.009..0.011 rows=4 loops=1)
                           ->  Sort  (cost=164.03..165.84 rows=721 width=158) (actual time=40.878..40.878 rows=4 loops=1)
                                 Sort Key: usage.geo_id
                                 Sort Method: quicksort  Memory: 25kB
                                 ->  Hash Join  (cost=50.49..129.81 rows=721 width=158) (actual time=4.191..40.862 rows=4 loops=1)
                                       Hash Cond: (usage.proto_id = proto.id)
                                       ->  Hash Join  (cost=8.90..62.72 rows=106 width=124) (actual time=4.157..40.826 rows=4 loops=1)
                                             Hash Cond: (usage.backend_id = backend.id)
                                             ->  Nested Loop  (cost=0.00..49.92 rows=69 width=118) (actual time=4.030..40.696 rows=4 loops=1)
                                                   ->  CTE Scan on usage  (cost=0.00..0.06 rows=3 width=87) (actual time=4.025..40.667 rows=4 loops=1)
                                                   ->  Append  (cost=0.00..16.59 rows=3 width=40) (actual time=0.004..0.006 rows=1 loops=4)
                                                         ->  Seq Scan on useragent  (cost=0.00..0.00 rows=1 width=66) (actual time=0.000..0.000 rows=0 loops=4)
                                                               Filter: (usage.useragent_id = id)
                                                         ->  Index Scan using useragent_2016_pkey on useragent_2016  (cost=0.28..8.30 rows=1 width=33) (actual time=0.002..0.003 rows=1 loops=4)
                                                               Index Cond: (id = usage.useragent_id)
                                                         ->  Index Scan using useragent_2017_pkey on useragent_2017  (cost=0.27..8.29 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=4)
                                                               Index Cond: (id = usage.useragent_id)
                                             ->  Hash  (cost=5.06..5.06 rows=307 width=6) (actual time=0.105..0.105 rows=336 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 22kB
                                                   ->  Append  (cost=0.00..5.06 rows=307 width=6) (actual time=0.004..0.063 rows=336 loops=1)
                                                         ->  Seq Scan on backend  (cost=0.00..0.00 rows=1 width=6) (actual time=0.000..0.000 rows=0 loops=1)
                                                         ->  Seq Scan on backend_2016  (cost=0.00..2.90 rows=190 width=6) (actual time=0.004..0.018 rows=194 loops=1)
                                                         ->  Seq Scan on backend_2017  (cost=0.00..2.16 rows=116 width=6) (actual time=0.005..0.015 rows=142 loops=1)
                                       ->  Hash  (cost=24.59..24.59 rows=1360 width=34) (actual time=0.020..0.020 rows=52 loops=1)
                                             Buckets: 2048  Batches: 1  Memory Usage: 18kB
                                             ->  Append  (cost=0.00..24.59 rows=1360 width=34) (actual time=0.002..0.011 rows=52 loops=1)
                                                   ->  Seq Scan on proto  (cost=0.00..0.00 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=1)
                                                   ->  Seq Scan on proto_2016  (cost=0.00..1.49 rows=49 width=34) (actual time=0.002..0.006 rows=49 loops=1)
                                                   ->  Seq Scan on proto_2017  (cost=0.00..23.10 rows=1310 width=34) (actual time=0.001..0.001 rows=3 loops=1)
                     ->  Append  (cost=0.00..16.74 rows=3 width=28) (actual time=0.007..0.009 rows=1 loops=4)
                           ->  Seq Scan on "time"  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=4)
                                 Filter: (usage.time_id = id)
                           ->  Index Scan using time_2016_pkey on time_2016  (cost=0.42..8.44 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=4)
                                 Index Cond: (id = usage.time_id)
                           ->  Index Scan using time_2017_pkey on time_2017  (cost=0.28..8.30 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=4)
                                 Index Cond: (id = usage.time_id)
               ->  Append  (cost=0.00..16.90 rows=3 width=29) (actual time=0.004..0.007 rows=1 loops=4)
                     ->  Seq Scan on referrer  (cost=0.00..0.00 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=4)
                           Filter: (usage.referrer_id = id)
                     ->  Index Scan using referrer_2016_pkey on referrer_2016  (cost=0.43..8.45 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=4)
                           Index Cond: (id = usage.referrer_id)
                     ->  Index Scan using referrer_2017_pkey on referrer_2017  (cost=0.43..8.45 rows=1 width=25) (actual time=0.002..0.002 rows=0 loops=4)
                           Index Cond: (id = usage.referrer_id)
         ->  Append  (cost=0.00..17.05 rows=3 width=86) (actual time=0.007..0.008 rows=1 loops=4)
               ->  Seq Scan on clienturi  (cost=0.00..0.00 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=4)
                     Filter: (usage.clienturi_id = id)
               ->  Index Scan using clienturi_2016_pkey1 on clienturi_2016  (cost=0.57..8.59 rows=1 width=89) (actual time=0.005..0.005 rows=1 loops=4)
                     Index Cond: (id = usage.clienturi_id)
               ->  Index Scan using clienturi_2017_pkey on clienturi_2017  (cost=0.43..8.45 rows=1 width=85) (actual time=0.002..0.002 rows=0 loops=4)
                     Index Cond: (id = usage.clienturi_id)
   ->  Append  (cost=0.00..17.05 rows=3 width=86) (actual time=0.004..0.005 rows=1 loops=4)
         ->  Seq Scan on clienturi t9  (cost=0.00..0.00 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=4)
               Filter: (usage.clienturi_parent_id = id)
         ->  Index Scan using clienturi_2016_pkey1 on clienturi_2016 t9_1  (cost=0.57..8.59 rows=1 width=89) (actual time=0.003..0.003 rows=1 loops=4)
               Index Cond: (id = usage.clienturi_parent_id)
         ->  Index Scan using clienturi_2017_pkey on clienturi_2017 t9_2  (cost=0.43..8.45 rows=1 width=85) (actual time=0.002..0.002 rows=0 loops=4)
               Index Cond: (id = usage.clienturi_parent_id)
 Planning time: 8.654 ms
 Execution time: 41.957 ms
...
Рейтинг: 0 / 0
10.01.2017, 15:35
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381603
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
vyegorov,

Да, партиционирование. Потому что требуется удалять устаревающие данные.

Если я правильно понял, основная фича pathman - динамический constraint exclusion.
...
Рейтинг: 0 / 0
10.01.2017, 15:38
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381604
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
LeXa NalBat,

Попробуй переписать запрос как (разделив план на 2 независимых блока
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
WITH _tmp AS (
select
    *
from
    usage
where
    usage.dim1_id in (1, 2) and
    usage.dim2_id in (2, 3) and
    usage.dim3_id in (3, 4) and
    usage.dim4_id in (4, 5) and
    usage.dim5_id in (5, 6) and
    usage.dim6_id in (6, 7)
)
SELECT * FROM _tmp AS usage
    inner join dim1 on (usage.dim1_id = dim1.id)
    inner join dim2 on (usage.dim2_id = dim2.id)
    inner join dim3 on (usage.dim3_id = dim3.id)
    inner join dim4 on (usage.dim4_id = dim4.id)
    inner join dim5 on (usage.dim5_id = dim5.id)
    inner join dim6 on (usage.dim6_id = dim6.id)
;



Если всеравно медленно - посмотри сколько времени планируется запрос в WITH блоке.
...
Рейтинг: 0 / 0
10.01.2017, 15:41
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381615
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Maxim Boguk,

Да, спасибо, так тоже получилось. Я писал выше: "вот еще получилось переформулировать через WITH".
...
Рейтинг: 0 / 0
10.01.2017, 15:55
| Ответить | Цитировать | Написать  
Медленное планирование запроса
    #39381622
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
LeXa NalBatMaxim Boguk,

Да, спасибо, так тоже получилось. Я писал выше: "вот еще получилось переформулировать через WITH".

Я пока написал ответ - там уже 4 новых коммента появилось.
...
Рейтинг: 0 / 0
10.01.2017, 16:03
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Медленное планирование запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39381513&tid=1996766]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 346ms
total:  452ms
созд. / загр.: 452ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]