Re: [pgsql-ru-general] не используется индекс

> EXPLAIN ANALYZE select * FROM orders_drivers od JOIN drivers d ON d.id = od.did WHERE sid = 2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=808.82..144710.55 rows=486762 width=169) (actual time=0.276..2673.383 rows=667453 loops=1)
Hash Cond: (od.did = d.id)
-> Seq Scan on orders_drivers od (cost=0.00..100792.89 rows=11365887 width=30) (actual time=0.015..1149.398 rows=11333318 loops=1)
-> Hash (cost=807.91..807.91 rows=260 width=139) (actual time=0.248..0.248 rows=260 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Bitmap Heap Scan on drivers d (cost=93.12..807.91 rows=260 width=139) (actual time=0.057..0.189 rows=260 loops=1)
Recheck Cond: (sid = 2)
-> Bitmap Index Scan on drivers_sid_idx (cost=0.00..93.05 rows=260 width=0) (actual time=0.044..0.044 rows=301 loops=1)
Index Cond: (sid = 2)
Total runtime: 2704.437 ms
(10 строк)

\d orders_drivers

Колонка | Тип | Модификаторы
---------+--------------+-------------------------------------------------------------
id | integer | NOT NULL DEFAULT nextval('orders_drivers_id_seq'::regclass)
oid | integer |
did | integer | NOT NULL
dist | numeric(8,3) |
time | integer |
tid | integer | NOT NULL
status | order_status | NOT NULL DEFAULT 'request'::order_status
Индексы:
"orders_drivers_pkey" PRIMARY KEY, btree (id)
"orders_drivers_oid_did_ukey" UNIQUE, btree (oid, did)
"orders_drivers_did_key" btree (did)
"orders_drivers_status_idx" btree (status)

\d drivers
> \d drivers
Таблица "public.drivers"
Колонка | Тип | Модификаторы
---------------+-----------------------------+------------------------
id | integer | NOT NULL
...
sid | integer | NOT NULL
...
Индексы:
"drivers_pkey" PRIMARY KEY, btree (id)
"drivers_auto_enabled_key" btree (auto_enabled) WHERE auto_enabled IS NOT NULL
"drivers_auto_order_key" btree (auto_order)
"drivers_is_bot_key" btree (is_bot) WHERE bot_latitude IS NOT NULL AND bot_longitude IS NOT NULL
"drivers_lp_time_key" btree (lp_time) WHERE lp_time IS NOT NULL
"drivers_removed_key" btree (removed)
"drivers_sid_idx" btree (sid)

почему по orders_drivers делается полный перебор?

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

по данным получается такая статистика:
выборка если бы использовался индекс выбирала бы по нему где-то около
1% записей. а так делается полный перебор.
и если добавить еще JOIN то запрос вообще уходит в аут на минуты.

каждый раз когда я заикаюсь в этой рассылке что мы что-то пытаемся
денормализовать мне говорят что я не прав и денормализовывать не
стоит.

а как его заставить выполнять запрос SQL тогда?
если выполнять двумя запросами (сперва выбираем всех drivers с
фильтром, затем все orders_drivers по did) то два запроса выполняются
практически мгновенно (0.08 сек). а вот с JOIN запрос выполняется 4
секунды.

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

1) VACUUM FULL ANALYZE orders_drivers

У вас идет промах где-то в 100 раз на этой таблице.

2) Если не поможет первое: Индекс на drivers id, sid, так как Пг считает что дешевле сначала sid отобрать, а потом построить хеш.

ЗЫ: Версия Пг какая?

Пятница, 28 декабря 2012, 22:44 +04:00 от "Dmitry E. Oboukhov" <unera(at)debian(dot)org>:
>> EXPLAIN ANALYZE select * FROM orders_drivers od JOIN drivers d ON d.id = od.did WHERE sid = 2;
>                                                               QUERY PLAN
>-----------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=808.82..144710.55 rows=486762 width=169) (actual time=0.276..2673.383 rows=667453 loops=1)
>   Hash Cond: (od.did = d.id)
>   -> Seq Scan on orders_drivers od (cost=0.00..100792.89 rows=11365887 width=30) (actual time=0.015..1149.398 rows=11333318 loops=1)
>   -> Hash (cost=807.91..807.91 rows=260 width=139) (actual time=0.248..0.248 rows=260 loops=1)
>         Buckets: 1024 Batches: 1 Memory Usage: 33kB
>         -> Bitmap Heap Scan on drivers d (cost=93.12..807.91 rows=260 width=139) (actual time=0.057..0.189 rows=260 loops=1)
>               Recheck Cond: (sid = 2)
>               -> Bitmap Index Scan on drivers_sid_idx (cost=0.00..93.05 rows=260 width=0) (actual time=0.044..0.044 rows=301 loops=1)
>                     Index Cond: (sid = 2)
> Total runtime: 2704.437 ms
>(10 строк)
>
>\d orders_drivers
>
> Колонка | Тип | Модификаторы
>---------+--------------+-------------------------------------------------------------
> id | integer | NOT NULL DEFAULT nextval('orders_drivers_id_seq'::regclass)
> oid | integer |
> did | integer | NOT NULL
> dist | numeric(8,3) |
> time | integer |
> tid | integer | NOT NULL
> status | order_status | NOT NULL DEFAULT 'request'::order_status
>Индексы:
>    "orders_drivers_pkey" PRIMARY KEY, btree (id)
>    "orders_drivers_oid_did_ukey" UNIQUE, btree (oid, did)
>    "orders_drivers_did_key" btree (did)
>    "orders_drivers_status_idx" btree (status)
>
>
>\d drivers
>> \d drivers
>                       Таблица "public.drivers"
>    Колонка | Тип | Модификаторы
>---------------+-----------------------------+------------------------
> id | integer | NOT NULL
>...
> sid | integer | NOT NULL
>...
>Индексы:
>    "drivers_pkey" PRIMARY KEY, btree (id)
>    "drivers_auto_enabled_key" btree (auto_enabled) WHERE auto_enabled IS NOT NULL
>    "drivers_auto_order_key" btree (auto_order)
>    "drivers_is_bot_key" btree (is_bot) WHERE bot_latitude IS NOT NULL AND bot_longitude IS NOT NULL
>    "drivers_lp_time_key" btree (lp_time) WHERE lp_time IS NOT NULL
>    "drivers_removed_key" btree (removed)
>    "drivers_sid_idx" btree (sid)
>
>
>почему по orders_drivers делается полный перебор?
>
>--
>
>. ''`. Dmitry E. Oboukhov
>: :’ : email: unera(at)debian(dot)org jabber: //UNera(at)uvw(dot)ru
>`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
>  `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>

> 1) VACUUM FULL ANALYZE orders_drivers

VACUUM FULL ANALYZE делал. на EXPLAIN'ы последующие не влияет никак

> У вас идет промах где-то в 100 раз на этой таблице.

эта таблица - взаимосвязь между orders и drivers.
соответственно выборка по did (in sid) в среднем в 100 раз меньше размера
таблицы, а выборка по одному did в среднем в 30000 раз меньше размера
таблицы

как бы что делать не знаю.
совершенно точно помню что когда данных было примерно в 10 раз меньше
EXPLAIN показывал использование индексов.
потом с какого-то момента начались тормоза и стали разбираться что у
нас тормозит и пришли вот к тому что explain показывать secscan начал

> 2) Если не поможет первое: Индекс на drivers id, sid, так как Пг считает что
> дешевле сначала sid отобрать, а потом построить хеш.

> ЗЫ: Версия Пг какая?

9.1.3

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

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

28.12.2012 22:28, Dmitry E. Oboukhov пишет:
> по данным получается такая статистика:
> выборка если бы использовался индекс выбирала бы по нему где-то около
> 1% записей. а так делается полный перебор.
> и если добавить еще JOIN то запрос вообще уходит в аут на минуты.
>
> каждый раз когда я заикаюсь в этой рассылке что мы что-то пытаемся
> денормализовать мне говорят что я не прав и денормализовывать не
> стоит.
>
> а как его заставить выполнять запрос SQL тогда?
> если выполнять двумя запросами (сперва выбираем всех drivers с
> фильтром, затем все orders_drivers по did) то два запроса выполняются
> практически мгновенно (0.08 сек). а вот с JOIN запрос выполняется 4
> секунды.
>
>

On Fri, Dec 28, 2012 at 10:44 AM, Dmitry E. Oboukhov <unera(at)debian(dot)org> wrote:
>> EXPLAIN ANALYZE select * FROM orders_drivers od JOIN drivers d ON d.id = od.did WHERE sid = 2;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=808.82..144710.55 rows=486762 width=169) (actual time=0.276..2673.383 rows=667453 loops=1)
> Hash Cond: (od.did = d.id)
> -> Seq Scan on orders_drivers od (cost=0.00..100792.89 rows=11365887 width=30) (actual time=0.015..1149.398 rows=11333318 loops=1)
> -> Hash (cost=807.91..807.91 rows=260 width=139) (actual time=0.248..0.248 rows=260 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 33kB
> -> Bitmap Heap Scan on drivers d (cost=93.12..807.91 rows=260 width=139) (actual time=0.057..0.189 rows=260 loops=1)
> Recheck Cond: (sid = 2)
> -> Bitmap Index Scan on drivers_sid_idx (cost=0.00..93.05 rows=260 width=0) (actual time=0.044..0.044 rows=301 loops=1)
> Index Cond: (sid = 2)
> Total runtime: 2704.437 ms
> (10 строк)

Можно посмотреть на explain analyze с enable_seqscan off?

>
> \d orders_drivers
>
> Колонка | Тип | Модификаторы
> ---------+--------------+-------------------------------------------------------------
> id | integer | NOT NULL DEFAULT nextval('orders_drivers_id_seq'::regclass)
> oid | integer |
> did | integer | NOT NULL
> dist | numeric(8,3) |
> time | integer |
> tid | integer | NOT NULL
> status | order_status | NOT NULL DEFAULT 'request'::order_status
> Индексы:
> "orders_drivers_pkey" PRIMARY KEY, btree (id)
> "orders_drivers_oid_did_ukey" UNIQUE, btree (oid, did)
> "orders_drivers_did_key" btree (did)
> "orders_drivers_status_idx" btree (status)
>
>
> \d drivers
>> \d drivers
> Таблица "public.drivers"
> Колонка | Тип | Модификаторы
> ---------------+-----------------------------+------------------------
> id | integer | NOT NULL
> ...
> sid | integer | NOT NULL
> ...
> Индексы:
> "drivers_pkey" PRIMARY KEY, btree (id)
> "drivers_auto_enabled_key" btree (auto_enabled) WHERE auto_enabled IS NOT NULL
> "drivers_auto_order_key" btree (auto_order)
> "drivers_is_bot_key" btree (is_bot) WHERE bot_latitude IS NOT NULL AND bot_longitude IS NOT NULL
> "drivers_lp_time_key" btree (lp_time) WHERE lp_time IS NOT NULL
> "drivers_removed_key" btree (removed)
> "drivers_sid_idx" btree (sid)
>
>
> почему по orders_drivers делается полный перебор?
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :' : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~' GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlDd6HIACgkQq4wAz/jiZTcjygCeLfeiDLf4sXFtDQ6NNU5uja4F
> +0IAoIL8fvUt8N+umms6NVpY+g5dCP4Y
> =IbeT
> -----END PGP SIGNATURE-----
>

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com