Poprawić wydajność COUNT / GROUP-BY w dużej tabeli PostgresSQL?

Używam PostgresSQL 9.2 i mam relację 12 kolumn z około 6 700 000 wierszy. Zawiera węzły w przestrzeni 3D, z których każdy odnosi się do użytkownika (który go utworzył). Aby zapytać, który użytkownik utworzył, ile węzłów wykonuję:explain analyze aby uzyskać więcej informacji):

EXPLAIN ANALYZE SELECT user_id, count(user_id) FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                    QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253668.70..253669.07 rows=37 width=8) (actual time=1747.620..1747.623 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220278.79 rows=6677983 width=8) (actual time=0.019..886.803 rows=6677983 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1747.653 ms

Jak widać, zajmuje to około 1,7 sekundy. Nie jest to takie złe, biorąc pod uwagę ilość danych, ale zastanawiam się, czy można to poprawić. Próbowałem dodać indeks BTree do kolumny użytkownika, ale to w żaden sposób nie pomogło.

Czy masz alternatywne sugestie?

Dla kompletności, jest to pełna definicja tabeli ze wszystkimi jej indeksami (bez ograniczeń klucza obcego, referencji i wyzwalaczy):

    Column     |           Type           |                      Modifiers                    
---------------+--------------------------+------------------------------------------------------
 id            | bigint                   | not null default nextval('concept_id_seq'::regclass)
 user_id       | bigint                   | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | bigint                   | not null
 location      | double3d                 | not null
 reviewer_id   | integer                  | not null default (-1)
 review_time   | timestamp with time zone |
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | double precision         | not null default 0
 confidence    | integer                  | not null default 5
 skeleton_id   | bigint                   |
Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_id_key" UNIQUE CONSTRAINT, btree (id)
    "skeleton_id_treenode_index" btree (skeleton_id)
    "treenode_editor_index" btree (editor_id)
    "treenode_location_x_index" btree (((location).x))
    "treenode_location_y_index" btree (((location).y))
    "treenode_location_z_index" btree (((location).z))
    "treenode_parent_id" btree (parent_id)
    "treenode_user_index" btree (user_id)

Edycja: Oto rezultat, gdy korzystam z zapytania (i indeksu) zaproponowanego przez @ypercube (bez zapytania zajmuje około 5,3 sekundy EXPLAIN ANALYZE):

EXPLAIN ANALYZE SELECT u.id, ( SELECT COUNT(*) FROM treenode AS t WHERE t.project_id=1 AND t.user_id = u.id ) AS number_of_nodes FROM auth_user As u;
                                                                        QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on auth_user u  (cost=0.00..6987937.85 rows=46 width=4) (actual time=29.934..5556.147 rows=46 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=151911.65..151911.66 rows=1 width=0) (actual time=120.780..120.780 rows=1 loops=46)
           ->  Bitmap Heap Scan on treenode t  (cost=4634.41..151460.44 rows=180486 width=0) (actual time=13.785..114.021 rows=145174 loops=46)
                 Recheck Cond: ((project_id = 1) AND (user_id = u.id))
                 Rows Removed by Index Recheck: 461076
                 ->  Bitmap Index Scan on treenode_user_index  (cost=0.00..4589.29 rows=180486 width=0) (actual time=13.082..13.082 rows=145174 loops=46)
                       Index Cond: ((project_id = 1) AND (user_id = u.id))
 Total runtime: 5556.190 ms
(9 rows)

Time: 5556.804 ms

Edycja 2: Jest to wynik, gdy używam opcji indexon project_id, user_id(ale bez optymalizacji schematu), jak sugerował @ erwin-brandstetter (zapytanie działa z 1,5 sekundy z tą samą prędkością, co moje oryginalne zapytanie):

EXPLAIN ANALYZE SELECT user_id, count(user_id) as ct FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                        QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253670.88..253671.24 rows=37 width=8) (actual time=1807.334..1807.339 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220280.62 rows=6678050 width=8) (actual time=0.183..893.491 rows=6678050 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1807.368 ms
(4 rows)

Głównym problemem jest brakujący indeks. Ale jest coś więcej.

SELECT user_id, count(*) AS ct
FROM   treenode
WHERE  project_id = 1
GROUP  BY user_id;

• Masz wiele bigintkolumn. Prawdopodobnie przesada. Zazwyczaj integerwystarcza na kolumny takie jak project_idi user_id. Pomogłoby to również w następnym elemencie.
  _{Optymalizując definicję tabeli, rozważ tę pokrewną odpowiedź, kładąc nacisk na wyrównanie danych i wypełnianie . Ale większość reszty ma również zastosowanie:}

  • Konfigurowanie PostgreSQL pod kątem wydajności odczytu

• Słoń w pokoju : nie ma indeksproject_id . Stworzyć jeden. Jest to ważniejsze niż reszta tej odpowiedzi.
  Będąc przy tym, uczyń indeks wielokolumnowy:

  CREATE INDEX treenode_project_id_user_id_index ON treenode (project_id, user_id);

  Jeśli zastosujesz się do mojej rady, integerbyłoby idealnie tutaj:

  • Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?

• user_idjest zdefiniowany NOT NULL, więc count(user_id)jest równoważny count(*), ale ten ostatni jest nieco krótszy i szybszy. (W tym konkretnym zapytaniu miałoby to nawet zastosowanie bez user_iddefinicji NOT NULL).

• idjest już kluczem podstawowym, dodatkowym UNIQUEograniczeniem jest bezużyteczny balast . Rzuć to:

  "treenode_pkey" PRIMARY KEY, btree (id)
  "treenode_id_key" UNIQUE CONSTRAINT, btree (id)

  _{Poza tym: nie użyłbym idjako nazwy kolumny. Użyj czegoś opisowego treenode_id.}

Dodano informacje

P: How many different project_id and user_id?
A:not more than five different project_id .

Oznacza to, że Postgres musi przeczytać około 20% całej tabeli, aby spełnić twoje zapytanie. O ile nie można użyć skanowania tylko do indeksu, skanowanie sekwencyjne w tabeli będzie szybsze niż w przypadku indeksów. Nie ma tu więcej wydajności do zyskania - poza optymalizacją ustawień tabeli i serwera.

Jeśli chodzi o skanowanie tylko za pomocą indeksu : aby zobaczyć, jak skuteczny może być, uruchom, VACUUM ANALYZEjeśli możesz sobie na to pozwolić (wyłącznie blokuje tabelę). Następnie spróbuj ponownie wykonać zapytanie. Teraz powinien być umiarkowanie szybciej za pomocą tylko indeks. Przeczytaj najpierw tę związaną odpowiedź:

• Zoptymalizuj proste zapytanie, używając daty i tekstu ORDER BY

Oprócz strony podręcznika dodanej do Postgres 9.6 i Wiki Postgres na temat skanów tylko do indeksu .

Najpierw dodam indeks, (project_id, user_id)a następnie w wersji 9.3, wypróbuj to zapytanie:

SELECT u.user_id, c.number_of_nodes 
FROM users AS u
   , LATERAL
     ( SELECT COUNT(*) AS number_of_nodes 
       FROM treenode AS t
       WHERE t.project_id = 1 
         AND t.user_id = u.user_id
     ) c 
-- WHERE c.number_of_nodes > 0 ;   -- you probably want this as well
                                   -- to show only relevant users

W wersji 9.2 spróbuj tego:

SELECT u.user_id, 
       ( SELECT COUNT(*) 
         FROM treenode AS t
         WHERE t.project_id = 1 
           AND t.user_id = u.user_id
       ) AS number_of_nodes  
FROM users AS u ;

Zakładam, że masz usersstolik. Jeśli nie, zamień na users:
(SELECT DISTINCT user_id FROM treenode)