Ускорить COUNT(*)

Sufir

Я не волшебник, я только учусь
PHP:
SELECT
    count(*)
FROM
    "th"."ticket" AS "self"
    JOIN "th"."user_question" AS "question" ON question.id = self.id
WHERE
    self.status = 1 AND question.trash = FALSE
Explain:
Код:
Aggregate  (cost=77913.98..77913.99 rows=1 width=0) (actual time=210.591..210.591 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..77861.94 rows=20816 width=0) (actual time=0.133..204.154 rows=21276 loops=1)
        ->  Index Scan using idx_th_ticket_status on ticket self  (cost=0.00..12774.44 rows=20816 width=4) (actual time=0.063..58.075 rows=21280 loops=1)
              Index Cond: (status = 1)
        ->  Index Scan using pk_th_user_question on user_question question  (cost=0.00..3.11 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=21280)
              Index Cond: (id = self.id)
              Filter: (NOT trash)
Total runtime: 240.652 ms
Четыре запроса - уже секунда, плюс десяток других, "лёгких" запросов, плюс отработка скрипта и уже полторы-две секунды на формирование страницы. Это очень много. Прегенерация не вариант, т.к. два десятка различных фильтров, на все комбинации не нагенеришь и актуальность требуется максимальная. Джойнов тоже может прибавляться в зависимости от фильтров, а соответственно дополнительные Nested Loops и время на запрос доходит до секунды-полутора.

Есть идеи что сделать можно? Денормализовать слив в одну таблицу или ещё что-то сделать можно?
 

fixxxer

К.О.
Партнер клуба
В данном случае должен сильно ускорить частичный индекс по where status=1, но "два десятка различных фильтров"...

А если зайти от from user_questions .. where exists(select 1 from ticket...), лучше план не становится?

UPD
А, ну еще если trash = TRUE много - можно добавить индекс
 

Sufir

Я не волшебник, я только учусь
Пока уберу счетчики в ajax, что б не тормозили страницу, хотя бы иллюзорное ускорение даст.
А, ну еще если trash = TRUE много - можно добавить индекс
Вот такой вариант дал небольшую прибавку: btree ("id") WHERE "trash"=FALSE; Total runtime: 199.074 ms, а если добавить по статусу, то возвращаемся обратно к 220-240 ms.

А если зайти от from user_questions .. where exists(select 1 from ticket...), лучше план не становится?
Только external merge Disk добавился.
Код:
Aggregate  (cost=69252.10..69252.11 rows=1 width=0) (actual time=232.015..232.015 rows=1 loops=1)
  ->  Nested Loop  (cost=14262.48..69200.11 rows=20797 width=0) (actual time=81.391..225.832 rows=21444 loops=1)
        ->  Unique  (cost=14262.48..14366.46 rows=20797 width=4) (actual time=81.294..104.136 rows=21448 loops=1)
              ->  Sort  (cost=14262.48..14314.47 rows=20797 width=4) (actual time=81.291..90.514 rows=21448 loops=1)
                    Sort Key: self.id
                    Sort Method: external merge  Disk: 288kB
                    ->  Index Scan using idx_th_ticket_status_trash on ticket self  (cost=0.00..12770.91 rows=20797 width=4) (actual time=0.056..57.855 rows=21448 loops=1)
                          Index Cond: (status = 1)
        ->  Index Scan using idx_th_user_question_id on user_question question  (cost=0.00..2.62 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=21448)
              Index Cond: (id = self.id)
Total runtime: 232.765 ms
Начинает шустро выполняться, когда добавляются сильно огрничивающие условия фильтрации по "th"."ticket" Total runtime: 0.124 ms как с джойном так и с EXISTS. Разницы особой нет.
 
Последнее редактирование:

Sufir

Я не волшебник, я только учусь
Действительно не понятно нафига постгрес тут сортирует...
 

Sufir

Я не волшебник, я только учусь
Код:
                             Table "th.th_ticket"
       Column       |              Type              |       Modifiers
--------------------+--------------------------------+------------------------
 id                 | integer                        | not null
 date_moderation    | timestamp without time zone    |
 moderator_id       | integer                        |
 date_close         | timestamp(0) without time zone |
 date_update        | timestamp(0) without time zone |
 status             | smallint                       | not null default 1
 handler_id         | integer                        |
 rejected           | boolean                        | not null default false
 processing_time    | integer                        | not null default 0
 answer_draft       | text                           |
 refuse             | smallint                       | not null default 0
 exists             | smallint                       | not null default 0
 connect            | smallint                       | not null default 0
 contact_list       | character varying(255)         |
 handler_comment    | text                           |
 date_to_moderation | timestamp(0) without time zone |
 th_visible         | boolean                        | not null default false
 is_suitable        | boolean                        | not null default false
Indexes:
    "th_ticket_pkey" PRIMARY KEY, btree (id)
    "th_ticket_id_key" UNIQUE CONSTRAINT, btree (id)
    "idx_th_ticket_handler_id" btree (handler_id)
    "idx_th_ticket_status" btree (status)
Foreign-key constraints:
    "th_ticket_id_fkey" FOREIGN KEY (id) REFERENCES th.th_user_question(id)


Код:
                  Table "th.th_user_question"
      Column      |              Type              | Modifiers
--------------------+--------------------------------+-----------
id                | integer                        | not null
allocation_id      | integer                        | not null
user_id            | integer                        | not null
theme_id          | smallint                      | not null
faq_allow_user    | boolean                        | not null
faq_date_add      | timestamp(0) without time zone |
date_create        | timestamp(0) without time zone | not null
user_ip            | cidr                          |
uid                | integer                        |
active            | boolean                        | not null
trash              | boolean                        | not null
updated            | bigint                        | not null
user_modified      | integer                        | not null
user_modified_sign | character varying(50)          | not null
old_id            | integer                        |
for_support        | boolean                        | not null
th_visible        | boolean                        | not null
processing        | smallint                      |
date_processing    | timestamp(0) without time zone |
Indexes:
    "pk_th_user_question" PRIMARY KEY, btree (id)
    "idx_th_user_question_allocation_id" btree (allocation_id)
    "idx_th_user_question_date_create" btree (date_create)
     "idx_th_user_question_id_untrashed" btree (id) WHERE trash = false
    "idx_th_user_question_theme_id" btree (theme_id)
    "idx_th_user_question_updated" btree (updated)
Referenced by:
    TABLE "th.th_ticket" CONSTRAINT "th_ticket_id_fkey" FOREIGN KEY (id) REFERENCES th.th_user_question(id)
 

Вложения

Последнее редактирование:

hell0w0rd

Продвинутый новичок
Код:
SELECT
count(*)
FROM
"th"."ticket" AS "self"
JOIN "th"."user_question" AS "question" ON question.id = self.id AND question.trash = FALSE
WHERE
self.status = 1
не?

Вообще, раз оптимизатор делает сортировку - можно поиграться с направлением индекса. Я тут недавно узнал, что у индекса можно задать направление, как у сортировки, и если оно не совпадает, индекс не используется.
 

antson

Новичок
Партнер клуба
@Sufir, я правильно понимаю у тебя одна сущность размазана по двум таблицам ?
Если да то напрашивается в th.th_user_question перенести статус или продублировать
И работать с одной таблицей без джоинов
 

Sufir

Я не волшебник, я только учусь
@hell0w0rd, нет, подобные эксперименты я в первую очередь пробовал. В данном случае планы полностью идентичные. Такие фишки дают эффект при LEFT JOIN.

я правильно понимаю у тебя одна сущность размазана по двум таблицам
Именно эти две таблиыв - да, основная таблица трансферится из другой БД и сохраняется в точности как оригинальная с другого проекта, а во второй хранятся данные специфичные для внутреннего проекта техподдержки (там не только статусы, много всякой служебной информации). Пока тупо в ajax счётчики вынес, страница грузится достаточно шустро, а счётчики отображаются чуть позже. На данный момент устраивает, поэтому пока отложил, других задач сейчас очень много. Но слить всё-же придётся со временем.
 
Сверху