Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы / PostgreSQL

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

12 сообщений из 12, страница 1 из 1

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603311

PavelTut

Гость

Получаем продукты, в отдельной таблице лежат склады с количеством товара, через stock_id связываемся с текущим активным складом.
Запрос:

Код: plsql

1.
2.
3.
4.
5.

SELECT p.* FROM product p
LEFT JOIN product_stock_value s ON p.stock_id = s.id
WHERE s.quantity > 0
ORDER BY p.rating DESC NULLS LAST
LIMIT 10

EXPLAIN:

Код: html

1.
2.
3.
4.
5.
6.
7.
8.
9.

 Limit  (cost=236924.04..236924.06 rows=10 width=1588)
   ->  Sort  (cost=236924.04..237900.76 rows=390691 width=1588)
         Sort Key: p.rating DESC NULLS LAST
         ->  Hash Join  (cost=13864.43..228481.34 rows=390691 width=1588)
               Hash Cond: (p.stock_id = s.id)
               ->  Seq Scan on product p  (cost=0.00..210915.78 rows=882784 width=1588)
               ->  Hash  (cost=12496.93..12496.93 rows=390714 width=4)
                     ->  Seq Scan on product_stock_value s  (cost=0.00..12496.93 rows=390714 width=4)
                           Filter: (quantity > 0)

Таблица product:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.2
-- Dumped by pg_dump version 9.6.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: product; Type: TABLE; Schema: public; Owner: siteadmin
--

CREATE TABLE product (
    id integer NOT NULL,
    parent_id integer,
    site_code character varying(2) NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    name text NOT NULL,
    code character varying(255) NOT NULL,
    description text,
    rating integer,
    year integer,
    stock_id integer,
    weight character varying(255),
    size_w integer,
    size_h integer,
    size_l integer,
    property_text text,
    counter integer,
    created_by integer,
    updated_by integer,
    created_at integer,
    updated_at integer,
    seo_h1 text,
    seo_title text,
    seo_description text,
    seo_keywords text,
    fts tsvector,
    img_path character varying(255),
    img_base_url character varying(255)
);


ALTER TABLE product OWNER TO siteadmin;

--
-- Name: product_id_seq; Type: SEQUENCE; Schema: public; Owner: siteadmin
--

CREATE SEQUENCE product_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE product_id_seq OWNER TO siteadmin;

--
-- Name: product_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: siteadmin
--

ALTER SEQUENCE product_id_seq OWNED BY product.id;


--
-- Name: product id; Type: DEFAULT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product ALTER COLUMN id SET DEFAULT nextval('product_id_seq'::regclass);


--
-- Name: product product_pkey; Type: CONSTRAINT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product
    ADD CONSTRAINT product_pkey PRIMARY KEY (id);


--
-- Name: fts_index; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX fts_index ON product USING gin (fts);


--
-- Name: p__code__ukey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE UNIQUE INDEX p__code__ukey ON product USING btree (code);


--
-- Name: p__counter__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__counter__ikey ON product USING btree (counter);


--
-- Name: p__name__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__name__ikey ON product USING btree (name);


--
-- Name: p__rating__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__rating__ikey ON product USING btree (rating);


--
-- Name: p__site_code__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__site_code__ikey ON product USING btree (site_code);


--
-- Name: p__site_code__status__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__site_code__status__ikey ON product USING btree (site_code, status);


--
-- Name: p__status__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__status__ikey ON product USING btree (status);


--
-- Name: p__stock_id__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__stock_id__ikey ON product USING btree (stock_id);


--
-- Name: p__updated_at__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__updated_at__ikey ON product USING btree (updated_at);


--
-- Name: p__year__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX p__year__ikey ON product USING btree (year);


--
-- Name: product product_fts_update; Type: TRIGGER; Schema: public; Owner: siteadmin
--

CREATE TRIGGER product_fts_update BEFORE INSERT OR UPDATE ON product FOR EACH ROW EXECUTE PROCEDURE product_vector_update();


--
-- PostgreSQL database dump complete
--

Таблица product_stock_value:

Код: plsql

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.2
-- Dumped by pg_dump version 9.6.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: product_stock_value; Type: TABLE; Schema: public; Owner: siteadmin
--

CREATE TABLE product_stock_value (
    id integer NOT NULL,
    product_id integer NOT NULL,
    stock_id character varying(255) NOT NULL,
    price double precision DEFAULT 0,
    currency character varying(3) NOT NULL,
    quantity integer DEFAULT 0,
    rate double precision DEFAULT 1,
    created_by integer,
    updated_by integer,
    created_at integer,
    updated_at integer
);


ALTER TABLE product_stock_value OWNER TO siteadmin;

--
-- Name: product_stock_value_id_seq; Type: SEQUENCE; Schema: public; Owner: siteadmin
--

CREATE SEQUENCE product_stock_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE product_stock_value_id_seq OWNER TO siteadmin;

--
-- Name: product_stock_value_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: siteadmin
--

ALTER SEQUENCE product_stock_value_id_seq OWNED BY product_stock_value.id;


--
-- Name: product_stock_value id; Type: DEFAULT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product_stock_value ALTER COLUMN id SET DEFAULT nextval('product_stock_value_id_seq'::regclass);


--
-- Name: product_stock_value product_stock_value_pkey; Type: CONSTRAINT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product_stock_value
    ADD CONSTRAINT product_stock_value_pkey PRIMARY KEY (id);


--
-- Name: psv__price__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX psv__price__ikey ON product_stock_value USING btree (price);


--
-- Name: psv__product_id__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX psv__product_id__ikey ON product_stock_value USING btree (product_id);


--
-- Name: psv__product_id__stock_id__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX psv__product_id__stock_id__ikey ON product_stock_value USING btree (product_id, stock_id);


--
-- Name: psv__quantity__ikey; Type: INDEX; Schema: public; Owner: siteadmin
--

CREATE INDEX psv__quantity__ikey ON product_stock_value USING btree (quantity);


--
-- Name: product_stock_value psv__product_id__fkey; Type: FK CONSTRAINT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product_stock_value
    ADD CONSTRAINT psv__product_id__fkey FOREIGN KEY (product_id) REFERENCES product(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: product_stock_value psv__stock_id__fkey; Type: FK CONSTRAINT; Schema: public; Owner: siteadmin
--

ALTER TABLE ONLY product_stock_value
    ADD CONSTRAINT psv__stock_id__fkey FOREIGN KEY (stock_id) REFERENCES product_stock(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- PostgreSQL database dump complete
--

Товаров 800к, запрос выполняется около 10 секунд.
Что можно сделать для оптимизации?

...

Рейтинг:

0 / 0

16.02.2018, 19:47

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603313

PavelTut

Гость

И возможно ли вообще? )
Можно конечно дублировать количество в основной таблице, но хотелось бы красиво.

...

Рейтинг:

0 / 0

16.02.2018, 19:49

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603316

PavelTut

Гость

Не могу понять почему тут:
-> Seq Scan on product p (cost=0.00..210915.78 rows=882784 width=1588)
И тут:
-> Seq Scan on product_stock_value s (cost=0.00..12496.93 rows=390714 width=4)
идёт перебор всех записей.

Ведь для stock_id и quantity созданы индексы.

...

Рейтинг:

0 / 0

16.02.2018, 19:54

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603340

sereginseregin

Гость

PavelTut,

Код: plsql

1.
2.

ORDER BY p.rating DESC NULLS LAST
LIMIT 10

Вероятно тут

YouTube Video

...

Рейтинг:

0 / 0

16.02.2018, 21:49

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603344

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

PavelTutНе могу понять почему тут:
-> Seq Scan on product p (cost=0.00..210915.78 rows=882784 width=1588)
И тут:
-> Seq Scan on product_stock_value s (cost=0.00..12496.93 rows=390714 width=4)
идёт перебор всех записей.

Ведь для stock_id и quantity созданы индексы.
так вы пляшете от рейтингов .

какое у вас распределение остатков ?
если 0 в остатках много -- оптимально -- присунуть признак нулевого остатка в табличку товаров -- "денормализация" -- для условных индексов (никто не собирается решать задачу кросс-табных индексов на уровне движков)

или кооптировать рейтинг в остатки -- с той же целью.

и подумать над условными индексами. после оного телодвижа.

если даже не денормализуя запихать с 2-х сторон составные инды "(рейт, ид)" и "(ид,остаток) где остаток >0", то возможно удастся пошить сносную иос--иос свертку с последующим чтением результатов для 10 отфильтрованных узлов.надо прикинуть по плотности 0. и вообще -- подумать, ага.

...

Рейтинг:

0 / 0

16.02.2018, 22:14

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603351

PavelTut

Гость

sereginseregin,
не, не тут.

...

Рейтинг:

0 / 0

16.02.2018, 22:30

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603357

PavelTut

Гость

sereginseregin,
о блин, спасибо, не заметил видео сразу.

...

Рейтинг:

0 / 0

16.02.2018, 22:38

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603366

ursido

Гость

PavelTut,

Код: sql

1.
2.
3.
4.
5.
6.

SELECT p.* 
FROM product p
INNER JOIN product_stock_value s ON p.stock_id = s.id
                                AND s.quantity > 0
ORDER BY p.rating DESC NULLS LAST
LIMIT 10

Этот запрос сколько выполняется?

...

Рейтинг:

0 / 0

16.02.2018, 23:01

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603367

PavelTut

Гость

qwwq,
Нулей примерно 5 часть. Денормализацию не представляю как тут. У одного товара может быть несколько остатков, на разных складах. Тут уже дублировать постоянно придётся, но не важно.

Спасибо, тормознул чего-то насчёт составных индексов.
С индексом 'rating', 'stock_id' в основной и 'id', 'quantity' в присоединенной таблице всё работает моментально.
Если ORDER BY p.rating DESC

Если же к ордеру добавить NULLS LAST то уже составные индексы не применяются, нужно разобраться.
Но тут в приложении сменю NULL на 0.

...

Рейтинг:

0 / 0

16.02.2018, 23:07

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603372

PavelTut

Гость

ursido,

Спасибо!
Немного поспешил. С сортировкой по рейтингу этот запрос остался таким же, хоть и срабатывают составные индексы (прошлый раз кеш бд сработал, а я уже обрадовался :) )

Код: plsql

1.
2.
3.
4.
5.

SELECT p.* FROM product p
LEFT JOIN product_stock_value s ON p.stock_id = s.id
WHERE s.quantity > 0
ORDER BY p.rating DESC
LIMIT 10

А этот моментально! Применяется только один составной индекс rating__stock_id

Код: plsql

1.
2.
3.
4.

SELECT p.* FROM product p
LEFT JOIN product_stock_value s ON p.stock_id = s.id AND s.quantity > 0
ORDER BY p.rating DESC
LIMIT 10

...

Рейтинг:

0 / 0

16.02.2018, 23:43

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603403

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

PavelTutПолучаем продукты, в отдельной таблице лежат склады с количеством товара, через stock_id связываемся с текущим активным складом.
Запрос:

Код: plsql

1.
2.
3.
4.
5.

SELECT p.* FROM product p
LEFT JOIN product_stock_value s ON p.stock_id = s.id
WHERE s.quantity > 0
ORDER BY p.rating DESC NULLS LAST
LIMIT 10

Товаров 800к, запрос выполняется около 10 секунд.
Что можно сделать для оптимизации?

Попробуйте
1)Добавить индекс на
on product(rating DESC NULLS LAST)

и переписать запрос как
SELECT p.* FROM product p
WHERE EXISTS (SELECT * FROM product_stock_value s WHERE p.stock_id = s.id AND s.quantity > 0)
ORDER BY p.rating DESC NULLS LAST
LIMIT 10

скорее всего получите index scan по новому индексу и nested loop по product_stock_value для фильтрации

для дополнительного ускорения я бы еще сделал уникальный (наверное если это id) индекс
ON product_stock_value(id) WHERE quantity > 0
тогда еще быстрее будет.

Ну и перед тем как делать explain analyze обязательно стоит сделать vacuum analyze На обе эти таблицы
(это вообще полезно делать после добавления индексов на всякий случай для получения внятной статистики).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

17.02.2018, 06:43

| Ответить | Цитировать | Написать

Нужно помощь в оптимизации простого LEFT JOIN запроса c WHERE из правой таблицы

#39603539

PavelTut

Гость

Maxim Boguk,

Спасибо. Глянул Ваше видео сверху, понял только первую часть про оффсеты и двойную сортировку (почти), дальше мрак :), не хватает знаний.

Я же поставил для рейтинга по-умолчанию значение 0 и отказался от NULLS LAST.
Установил для теста минимальное значение shared_buffers = 128kB (для тестирования это правильно?)
Ваш пример с вложенным селектом и с джойном работают примерно одинаково:

Код: plsql

1.
2.
3.
4.
5.

SELECT p.* FROM product p
WHERE EXISTS (SELECT * FROM product_stock_value s WHERE p.stock_id = s.id AND s.quantity > 0)
AND  ("p"."site_code"='s1') AND ("p"."status"=1)
ORDER BY p.rating DESC
LIMIT 10

С джойном:

Код: plsql

1.
2.
3.
4.
5.

SELECT p.* FROM product p
LEFT JOIN product_stock_value s ON p.stock_id = s.id
WHERE (p.site_code='s1') AND (p.status=1)  AND (s.quantity > 0)
ORDER BY p.rating DESC
LIMIT 10

EXPLAIN ANALYZE

Код: html

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

Limit  (cost=0.85..26.00 rows=10 width=1583) (actual time=0.078..0.257 rows=10 loops=1)

  ->  Nested Loop  (cost=0.85..850186.76 rows=337999 width=1583) (actual time=0.077..0.254 rows=10 loops=1)

        ->  Index Scan Backward using p__rating__site_code__status__ikey on product p  (cost=0.42..508265.13 rows=779361 width=1583) (actual time=0.037..0.118 rows=10 loops=1)

              Index Cond: (((site_code)::text = 's1'::text) AND (status = 1))

        ->  Index Only Scan using psv__id__quantity__ikey on product_stock_value s  (cost=0.42..0.44 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=10)

              Index Cond: ((id = p.stock_id) AND (quantity > 0))

              Heap Fetches: 1

Planning time: 1.413 ms
Execution time: 0.331 ms

Во вложенной создал индекс id__quantity, в основной rating__site_code__status и всё замечательно!

...

Рейтинг:

0 / 0

17.02.2018, 19:51

| Ответить | Цитировать | Написать

12 сообщений из 12, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39603403&tid=1995938]:	0ms
get settings:	11ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	37ms
get topic data:	11ms
get forum data:	3ms
get page messages:	50ms
get tp. blocked users:	1ms
others:	18ms

total:	153ms