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, 131533 estimated 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 estimated total 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.158 rows=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)
