Postgres wykonuje skanowanie sekwencyjne zamiast skanowania indeksu

Mam tabelę z około 10 milionami wierszy i indeksem w polu daty. Kiedy próbuję wyodrębnić unikalne wartości zindeksowanego pola, Postgres uruchamia skanowanie sekwencyjne, mimo że zestaw wyników zawiera tylko 26 elementów. Dlaczego optymalizator wybiera ten plan? A jak mogę tego uniknąć?

Z innych odpowiedzi podejrzewam, że jest to tak samo związane z zapytaniem, jak z indeksem.

explain select "labelDate" from pages group by "labelDate";
                              QUERY PLAN
-----------------------------------------------------------------------
 HashAggregate  (cost=524616.78..524617.04 rows=26 width=4)
   Group Key: "labelDate"
   ->  Seq Scan on pages  (cost=0.00..499082.42 rows=10213742 width=4)
(3 rows)

Struktura tabeli:

http=# \d pages
                                       Table "public.pages"
     Column      |          Type          |        Modifiers
-----------------+------------------------+----------------------------------
 pageid          | integer                | not null default nextval('...
 createDate      | integer                | not null
 archive         | character varying(16)  | not null
 label           | character varying(32)  | not null
 wptid           | character varying(64)  | not null
 wptrun          | integer                | not null
 url             | text                   |
 urlShort        | character varying(255) |
 startedDateTime | integer                |
 renderStart     | integer                |
 onContentLoaded | integer                |
 onLoad          | integer                |
 PageSpeed       | integer                |
 rank            | integer                |
 reqTotal        | integer                | not null
 reqHTML         | integer                | not null
 reqJS           | integer                | not null
 reqCSS          | integer                | not null
 reqImg          | integer                | not null
 reqFlash        | integer                | not null
 reqJSON         | integer                | not null
 reqOther        | integer                | not null
 bytesTotal      | integer                | not null
 bytesHTML       | integer                | not null
 bytesJS         | integer                | not null
 bytesCSS        | integer                | not null
 bytesHTML       | integer                | not null
 bytesJS         | integer                | not null
 bytesCSS        | integer                | not null
 bytesImg        | integer                | not null
 bytesFlash      | integer                | not null
 bytesJSON       | integer                | not null
 bytesOther      | integer                | not null
 numDomains      | integer                | not null
 labelDate       | date                   |
 TTFB            | integer                |
 reqGIF          | smallint               | not null
 reqJPG          | smallint               | not null
 reqPNG          | smallint               | not null
 reqFont         | smallint               | not null
 bytesGIF        | integer                | not null
 bytesJPG        | integer                | not null
 bytesPNG        | integer                | not null
 bytesFont       | integer                | not null
 maxageMore      | smallint               | not null
 maxage365       | smallint               | not null
 maxage30        | smallint               | not null
 maxage1         | smallint               | not null
 maxage0         | smallint               | not null
 maxageNull      | smallint               | not null
 numDomElements  | integer                | not null
 numCompressed   | smallint               | not null
 numHTTPS        | smallint               | not null
 numGlibs        | smallint               | not null
 numErrors       | smallint               | not null
 numRedirects    | smallint               | not null
 maxDomainReqs   | smallint               | not null
 bytesHTMLDoc    | integer                | not null
 maxage365       | smallint               | not null
 maxage30        | smallint               | not null
 maxage1         | smallint               | not null
 maxage0         | smallint               | not null
 maxageNull      | smallint               | not null
 numDomElements  | integer                | not null
 numCompressed   | smallint               | not null
 numHTTPS        | smallint               | not null
 numGlibs        | smallint               | not null
 numErrors       | smallint               | not null
 numRedirects    | smallint               | not null
 maxDomainReqs   | smallint               | not null
 bytesHTMLDoc    | integer                | not null
 fullyLoaded     | integer                |
 cdn             | character varying(64)  |
 SpeedIndex      | integer                |
 visualComplete  | integer                |
 gzipTotal       | integer                | not null
 gzipSavings     | integer                | not null
 siteid          | numeric                |
Indexes:
    "pages_pkey" PRIMARY KEY, btree (pageid)
    "pages_date_url" UNIQUE CONSTRAINT, btree ("urlShort", "labelDate")
    "idx_pages_cdn" btree (cdn)
    "idx_pages_labeldate" btree ("labelDate") CLUSTER
    "idx_pages_urlshort" btree ("urlShort")
Triggers:
    pages_label_date BEFORE INSERT OR UPDATE ON pages
      FOR EACH ROW EXECUTE PROCEDURE fix_label_date()

Jest to znany problem dotyczący optymalizacji Postgres. Jeśli odrębnych wartości jest niewiele - jak w twoim przypadku - i jesteś w wersji 8.4+, bardzo szybkie obejście za pomocą kwerendy rekurencyjnej jest opisane tutaj: Loose Indexscan .

Twoje zapytanie może zostać przepisane ( LATERALwymaga wersji 9.3+):

WITH RECURSIVE pa AS 
( ( SELECT labelDate FROM pages ORDER BY labelDate LIMIT 1 ) 
  UNION ALL
    SELECT n.labelDate 
    FROM pa AS p
         , LATERAL 
              ( SELECT labelDate 
                FROM pages 
                WHERE labelDate > p.labelDate 
                ORDER BY labelDate 
                LIMIT 1
              ) AS n
) 
SELECT labelDate 
FROM pa ;

Erwin Brandstetter ma dokładne wyjaśnienie i kilka wariantów zapytania w tej odpowiedzi (w pokrewnym, ale innym problemie): Zoptymalizuj zapytanie GROUP BY, aby pobrać najnowszy rekord dla użytkownika

Najlepsze zapytanie bardzo zależy od dystrybucji danych .

Masz wiele wierszy na dzień, co zostało ustalone. Ponieważ twoja obudowa spala się tylko do 26 wartości, wszystkie poniższe rozwiązania będą niesamowicie szybkie, jak tylko użyje się indeksu.
(W przypadku bardziej wyraźnych wartości sprawa stałaby się bardziej interesująca).

W ogóle nie ma potrzeby angażowania pageid się (jak skomentowałeś).

Indeks

Wszystko czego potrzebujesz to prosty indeks btree "labelDate".
W przypadku więcej niż kilku wartości NULL w kolumnie częściowy indeks pomaga nieco więcej (i jest mniejszy):

CREATE INDEX pages_labeldate_nonull_idx ON big ("labelDate")
WHERE  "labelDate" IS NOT NULL;

Później wyjaśniłeś:

0% NULL, ale dopiero po naprawieniu problemów podczas importowania.

Indeks częściowy może nadal mieć sens, aby wykluczyć stany pośrednie wierszy o wartości NULL. Pozwoliłoby to uniknąć niepotrzebnych aktualizacji indeksu (powodując wzdęcia).

Pytanie

Oparty na tymczasowym zakresie

Jeśli Twoje daty pojawiają się w ciągłym zakresie i nie ma zbyt wielu luk , możemy skorzystać z charakteru typu danych datena naszą korzyść. Istnieje tylko skończona, policzalna liczba wartości między dwiema podanymi wartościami. Jeśli odstępów jest niewiele, będzie to najszybsze:

SELECT d."labelDate"
FROM  (
   SELECT generate_series(min("labelDate")::timestamp
                        , max("labelDate")::timestamp
                        , interval '1 day')::date AS "labelDate"
   FROM   pages
   ) d
WHERE  EXISTS (SELECT FROM pages WHERE "labelDate" = d."labelDate");

Dlaczego oddanych do timestampw generate_series()? Widzieć:

• Generowanie szeregów czasowych między dwiema datami w PostgreSQL

Min i max można tanio wybrać z indeksu. Jeśli znasz minimalną i / lub maksymalną możliwą datę, jest ona jeszcze nieco tańsza. Przykład:

SELECT d."labelDate"
FROM  (SELECT date '2011-01-01' + g AS "labelDate"
       FROM   generate_series(0, now()::date - date '2011-01-01' - 1) g) d
WHERE  EXISTS (SELECT FROM pages WHERE "labelDate" = d."labelDate");

Lub na niezmienny interwał:

SELECT d."labelDate"
FROM  (SELECT date '2011-01-01' + g AS "labelDate"
       FROM generate_series(0, 363) g) d
WHERE  EXISTS (SELECT FROM pages WHERE "labelDate" = d."labelDate");

Luźne skanowanie indeksu

Działa to bardzo dobrze z każdym rozkładem dat (o ile mamy wiele wierszy na datę). Zasadniczo to, co już @ypercube . Ale są pewne dobre punkty i musimy zadbać o to, aby nasz ulubiony indeks mógł być używany wszędzie.

WITH RECURSIVE p AS (
   ( -- parentheses required for LIMIT
   SELECT "labelDate"
   FROM   pages
   WHERE  "labelDate" IS NOT NULL
   ORDER  BY "labelDate"
   LIMIT  1
   ) 
   UNION ALL
   SELECT (SELECT "labelDate" 
           FROM   pages 
           WHERE  "labelDate" > p."labelDate" 
           ORDER  BY "labelDate" 
           LIMIT  1)
   FROM   p
   WHERE  "labelDate" IS NOT NULL
   ) 
SELECT "labelDate" 
FROM   p
WHERE  "labelDate" IS NOT NULL;

• Pierwszy CTE pjest faktycznie taki sam jak

  SELECT min("labelDate") FROM pages

  Ale pełna forma upewnia się, że używany jest nasz indeks częściowy. Co więcej, ten formularz jest zazwyczaj nieco szybszy w moim doświadczeniu (iw moich testach).

• W przypadku tylko jednej kolumny skorelowane podzapytania w rekurencyjnym terminie rCTE powinny być nieco szybsze. Wymaga to wykluczenia wierszy, których wynikiem jest NULL dla „labelDate”. Widzieć:

• Zoptymalizuj zapytanie GROUP BY, aby pobrać najnowszy rekord na użytkownika

Na bok

Niewymienione, legalne, małe litery ułatwiają życie.
Uporządkuj kolumny w definicji tabeli, aby zaoszczędzić trochę miejsca na dysku:

• Obliczanie i oszczędzanie miejsca w PostgreSQL

Z dokumentacji postgresql:

CLUSTER może ponownie sortować tabelę za pomocą skanowania indeksu określonego indeksu lub (jeśli indeks jest drzewem b) skanowania sekwencyjnego, a następnie sortowania . Podejmie próbę wybrania metody, która będzie szybsza, w oparciu o parametry kosztu planisty i dostępne informacje statystyczne.

Twój indeks na labelDate to btree ..

Odniesienie:

http://www.postgresql.org/docs/9.1/static/sql-cluster.html