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

On Fri, Dec 28, 2012 at 10:44 AM, Dmitry E. Oboukhov <unera@debian.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=11333318loops=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=260loops=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.044rows=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@debian.org jabber://UNera@uvw.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.ru@gmail.com