Przeprowadzam aktualizację, w której wymagam dokładnej równości tstzrangezmiennej. ~ 1M wierszy jest modyfikowanych, a zapytanie zajmuje ~ 13 minut. Wynik EXPLAIN ANALYZEmożna zobaczyć tutaj , a rzeczywiste wyniki są bardzo różne od tych oszacowanych przez narzędzie do planowania zapytań. Problem polega na tym, że podczas skanowania indeksu t_rangeoczekuje się zwrócenia jednego wiersza.
Wydaje się to być związane z faktem, że statystyki dotyczące typów zakresów są przechowywane inaczej niż statystyki innych typów. Patrząc w pg_statswidoku w kolumnie n_distinctwynosi 1, a pozostałe obszary (na przykład most_common_vals, most_common_freqs) są puste.
Jednak gdzieś muszą być przechowywane statystyki t_range. Niezwykle podobna aktualizacja, w której używam „wewnątrz” na t_range zamiast dokładnej równości, zajmuje około 4 minut i używa zasadniczo innego planu zapytań (patrz tutaj ). Drugi plan zapytań ma dla mnie sens, ponieważ zostanie użyty każdy wiersz w tabeli tymczasowej i znaczna część tabeli historii. Co ważniejsze, narzędzie do planowania zapytań przewiduje w przybliżeniu prawidłową liczbę wierszy dla włączonego filtra t_range.
Dystrybucja t_rangejest nieco niezwykła. Używam tej tabeli do przechowywania stanu historycznego innej tabeli, a zmiany w drugiej tabeli pojawiają się naraz w dużych zrzutach, więc nie ma wielu różnych wartości t_range. Oto liczby odpowiadające każdej z unikalnych wartości t_range:
t_range | count
-------------------------------------------------------------------+---------
["2014-06-12 20:58:21.447478+00","2014-06-27 07:00:00+00") | 994676
["2014-06-12 20:58:21.447478+00","2014-08-01 01:22:14.621887+00") | 36791
["2014-06-27 07:00:00+00","2014-08-01 07:00:01+00") | 1000403
["2014-06-27 07:00:00+00",infinity) | 36791
["2014-08-01 07:00:01+00",infinity) | 999753Liczby dla różnych t_rangepowyżej są kompletne, więc liczność wynosi ~ 3 mln (z których ~ 1 mln będzie miało wpływ jedno z zapytań o aktualizację).
Dlaczego zapytanie 1 działa znacznie słabiej niż zapytanie 2? W moim przypadku zapytanie 2 jest dobrym zamiennikiem, ale jeśli naprawdę wymagana była równość dokładnych zakresów, jak mogę zmusić Postgres do korzystania z inteligentniejszego planu zapytań?
Definicja tabeli z indeksami (usuwanie niepotrzebnych kolumn):
Column | Type | Modifiers
---------------------+-----------+------------------------------------------------------------------------------
history_id | integer | not null default nextval('gtfs_stop_times_history_history_id_seq'::regclass)
t_range | tstzrange | not null
trip_id | text | not null
stop_sequence | integer | not null
shape_dist_traveled | real |
Indexes:
"gtfs_stop_times_history_pkey" PRIMARY KEY, btree (history_id)
"gtfs_stop_times_history_t_range" gist (t_range)
"gtfs_stop_times_history_trip_id" btree (trip_id)Zapytanie 1:
UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND sth.t_range = '["2014-08-01 07:00:01+00",infinity)'::tstzrange;Zapytanie 2:
UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND '2014-08-01 07:00:01+00'::timestamptz <@ sth.t_range;Aktualizacje Q1 999753 wierszy i aktualizacje Q2 999753 + 36791 = 1036544 (tj. Tabela temp jest taka, że każdy wiersz spełniający warunek zakresu czasu jest aktualizowany).
Próbowałem tego zapytania w odpowiedzi na komentarz @ ypercube :
Zapytanie 3:
UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND sth.t_range <@ '["2014-08-01 07:00:01+00",infinity)'::tstzrange
AND '["2014-08-01 07:00:01+00",infinity)'::tstzrange <@ sth.t_range;Plan zapytań i wyniki (patrz tutaj ) były pośrednie między dwoma poprzednimi przypadkami (~ 6 minut).
EDYCJA 2016/02/05
Po 1,5 roku nie mam już dostępu do danych, stworzyłem tabelę testową o tej samej strukturze (bez indeksów) i podobnej liczności. Odpowiedź jjanesa sugerowała, że przyczyną może być uporządkowanie tabeli tymczasowej użytej do aktualizacji. Nie byłem w stanie przetestować tej hipotezy bezpośrednio, ponieważ nie mam dostępu do track_io_timing(za pomocą Amazon RDS).
Ogólne wyniki były znacznie szybsze (kilkakrotnie). Zgaduję, że dzieje się tak z powodu usunięcia indeksów, zgodnie z odpowiedzią Erwina .
W tym przypadku testowym kwerendy 1 i 2 zajmowały zasadniczo tyle samo czasu, ponieważ oba używały łączenia scalającego. Oznacza to, że nie byłem w stanie uruchomić niczego, co spowodowało, że Postgres wybrał łączenie mieszające, więc nie mam pojęcia, dlaczego Postgres wybrał słabo działające łączenie mieszające.
(lower(t_range),upper(t_range))ponieważ sprawdzasz równość.
(a = b)do dwóch „zawiera” warunki:(a @> b AND b @> a)? Czy plan się zmienia?