Chcę mieć zmienną buforowaną podczas zapytań wykonujących na Postgres 12 . Podążyłem za podejściem do CTE jak poniżej:

-- BEGIN PART 1
with cached_vars as (
    select max(datetime) as datetime_threshold
    from locations
    where distance > 70
      and user_id = 9087
)
-- END PART 1
-- BEGIN PART 2
select *
from locations
where user_id = 9087
  and datetime > (select datetime_threshold from cached_vars)
-- END PART 2

Uruchamianie powyższego zapytania doprowadzi do kwestii wydajności. Spodziewałem się całkowitego wykonawczego w przybliżeniu równe {X0}}, ale trwa wiele dłużej.

W szczególności nie ma problemu z wydajnością, gdy uruchomię tylko drugą część z ręcznym {x0}}.

Tabela locations jest zdefiniowany jako:

 id | user_id | datetime | location | distance | ...
-----------------------------------------------------

Czy jest jakiś sposób, aby zmniejszyć całkowity czas wykonania do czegoś takiego jak (part1 runtime + part2 runtime)?

0
partizaans 14 marzec 2021, 17:34

4 odpowiedzi

Najlepsza odpowiedź

Wyjaśnienie za obserwowaną różnicą jest to:

Postgre ma statystyki kolumnowe i mogą dostosować plan zapytania w zależności od wartości dostarczonej stałej dla datetime_threshold. Przy korzystnych wartościach filtra może to prowadzić do znacznie wydajnego planu zapytania.

W innym przypadku, gdy datetime_threshold musi być obliczany w innym SELECT, Postgres musi być domyślnie do generycznego planu. datetime_threshold może być cokolwiek.

Różnica staną się oczywiste w wyjściu EXPLAIN.

Aby upewnić się, że Postgre optymalizuje drugą część wartości rzeczywistej {X0}}, można uruchomić dwa oddzielne zapytania (zasilaj wynik zapytania 1 jako stałej do zapytania 2) lub użyj dynamicznego SQL, aby wymusić ponowne planowanie zapytania 2 Za każdym razem w funkcji PL / PGSQL.

Na przykład

CREATE OR REPLACE FUNCTION foo(_user_id int, _distance int = 70)
  RETURNS SETOF locations
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
     'SELECT *
      FROM   locations
      WHERE  user_id = $1
      AND    datetime > $2'
   USING _user_id
      , (SELECT max(datetime)
         FROM   locations
         WHERE  distance > _distance
         AND    user_id = _user_id);
END
$func$;

Połączenie:

SELECT * FROM foo(9087);

Związane z:

W skrajnych przypadkach możesz nawet użyć innego dynamicznego zapytania, aby obliczyć datetime_threshold. Ale nie spodziewam się, że to konieczne.

Jeśli chodzi o "coś użytecznego w dokumentach" :

[...] Ważną różnicą jest to, że EXECUTE ponownie zaplanuje polecenie na każdym wykonaniu, generując plan specyficzny dla Aktualne wartości parametrów; natomiast pl / pgsql może w przeciwnym razie utworzyć Ogólny plan i pamięć podręczna do ponownego użycia. w sytuacjach, w których najlepsze Plan zależy silnie na wartościach parametrów, może być pomocne Użyj EXECUTE, aby pozytywnie upewnić się, że plan ogólny nie zostanie wybrany.

Pogrubiona kopalnia naciska.

Indeksy

Doskonałe indeksy byłyby:

CREATE INDEX ON locations (user_id, distance DESC NULL LAST, date_time DESC NULLS LAST); -- for query 1
CREATE INDEX ON locations (user_id, date_time);           -- for query 2

Drobne strojenie zależy od nieujawnionych szczegółów. Częściowy indeks może być opcją.

Może być dowolna liczba dodatkowych powodów, dla których zapytanie jest powolne. Za mało szczegółów.

2
Erwin Brandstetter 14 marzec 2021, 17:06

Jeśli chcesz, aby Twoje zapytanie do dobrego wykonania, sugeruję dodanie indeksów locations(user_id, distance) i locations(user_id, datetime).

Wyrafiłbym również zapytanie za pomocą funkcji okiennych:

select l.*
from (select l.*,
             max(datetime) filter (where distance > 70) over (partition by userid) as datetime_threshold
      from location l
      where userid = 9087
     ) l
where datetime > datetime_threshold;

Funkcje okien często poprawiają wydajność. Jednak z odpowiednimi indeksami nie wiem, czy dwie wersje będą zasadniczo inne.

0
Gordon Linoff 14 marzec 2021, 14:40

Przełam zapytanie do dwóch części i przechowuj pierwszą część w tabeli Temp (Tymczasowa tabela w PostgreSQL jest dostępna tylko w bieżącej sesji bazy danych). Następnie dołącz do tabeli Temp z drugą częścią. Mam nadzieję, że przyspieszy czas przetwarzania.

 CREATE TEMPORARY TABLE temp_table_cached_vars (
       datetime_threshold timestamp
    );
    
    -- BEGIN PART 1
    with cached_vars as (
        select max(datetime) as datetime_threshold
        from locations
        where distance > 70
          and user_id = 9087
    )insert into temp_table_name select datetime_threshold from cached_vars 
    -- END PART 1
    -- BEGIN PART 2
    select *
    from locations
    where user_id = 9087
      and datetime > (select datetime_threshold from temp_table_cached_vars Limit 1)

-- END PART 2
0
Kazi Mohammad Ali Nur 14 marzec 2021, 17:03

Wystarczy dodać LIMI1 w podzewności, gdy używałem poniżej przykładu.

-- BEGIN PART 1
with cached_vars as (
    select max(datetime) as datetime_threshold
    from locations
    where distance > 70
      and user_id = 9087
)
-- END PART 1
-- BEGIN PART 2
select *
from locations
where user_id = 9087
  and datetime > (select datetime_threshold from cached_vars Limit 1)
-- END PART 2
0
Kazi Mohammad Ali Nur 14 marzec 2021, 17:20