Why won't it index scan?

Поиск
Список
Период
Сортировка
От Ed L.
Тема Why won't it index scan?
Дата
Msg-id 200605171037.32907.pgsql@bluepolka.net
обсуждение исходный текст
Ответы Re: Why won't it index scan?  ("John D. Burger" <john@mitre.org>)
Re: Why won't it index scan?  ("Ed L." <pgsql@bluepolka.net>)
Список pgsql-general
Can someone help me understand why the 8.1.2 query below is
using a seq scan instead of an index scan?  All relevant
columns appear to be indexed and all tables vacuum analyzed.

$ psql -c "analyze verbose patient"
INFO:  analyzing "public.patient"
INFO:  "patient": scanned 3000 of 3353 pages, containing 117685 live rows and 5471 dead rows; 3000 rows in sample,
131533estimated total rows 
ANALYZE

$ psql -c "select count(1) from patient"
                  
 count
--------
 131661
(1 row)


$ psql -c "analyze verbose visit"
INFO:  analyzing "public.visit"
INFO:  "visit": scanned 3000 of 19985 pages, containing 58520 live rows and 7045 dead rows; 3000 rows in sample, 389841
estimatedtotal rows 
ANALYZE

$ psql -c "select count(1) from visit"
 count
--------
 389102
(1 row)


$ psql -c "explain analyze select * from visit inner join patient on patient.key = visit.patient_key where
nursestation_key= '40';" 
                                                                    QUERY PLAN
                           

---------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=27724.37..28457.01 rows=4956 width=421) (actual time=1819.993..2004.802 rows=6 loops=1)
   Merge Cond: ("outer".patient_key = "inner"."key")
   ->  Sort  (cost=11859.31..11871.70 rows=4956 width=209) (actual time=0.416..0.426 rows=6 loops=1)
         Sort Key: visit.patient_key
         ->  Bitmap Heap Scan on visit  (cost=69.35..11555.14 rows=4956 width=209) (actual time=0.187..0.245 rows=6
loops=1)
               Recheck Cond: (nursestation_key = 40)
               ->  Bitmap Index Scan on idx_visit_nursestation_key  (cost=0.00..69.35 rows=4956 width=0) (actual
time=0.158..0.158rows=6 loops=1) 
                     Index Cond: (nursestation_key = 40)
   ->  Sort  (cost=15865.05..16194.21 rows=131661 width=212) (actual time=1768.501..1856.334 rows=61954 loops=1)
         Sort Key: patient."key"
         ->  Seq Scan on patient  (cost=0.00..4669.61 rows=131661 width=212) (actual time=0.010..355.299 rows=131661
loops=1)
 Total runtime: 2046.323 ms
(12 rows)


                                         Table "public.patient"
      Column       |            Type             |                       Modifiers
-------------------+-----------------------------+-------------------------------------------------------
 key               | integer                     | not null default nextval('patient_key_seq'::regclass)
...
Indexes:
    "pk_patient" PRIMARY KEY, btree ("key")
...

                                           Table "public.visit"
        Column         |            Type             |                      Modifiers
-----------------------+-----------------------------+-----------------------------------------------------
 patient_key           | integer                     | not null
 nursestation_key      | integer                     |
...
Indexes:
    "idx_visit_nursestation_key" btree (nursestation_key)
    "idx_visit_patient_key" btree (patient_key)

В списке pgsql-general по дате отправления:

Предыдущее
От: Terry Lee Tucker
Дата:
Сообщение: Re: DB structure of PostGRE
Следующее
От: "John D. Burger"
Дата:
Сообщение: Re: Why won't it index scan?