Hi,
I am trying
to perform the following type of query 'select patientname ... from
patient were patientname LIKE 'JONES%, %' order by patientname asc limit 100'.
There about 1.4 million rows in the table. On my windows machine (2GB Ram ,3Ghz,
Windows XP, 120GB Hard Drive, postgres 8.1beta4) it takes about 150 millisecs
and the query plan is
'Limit (cost=18381.90..18384.40 rows=100 width=404)'
' -> Unique (cost=18381.90..18418.62 rows=1469 width=404)'
' -> Sort (cost=18381.90..18385.57 rows=1469 width=404)'
' Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate'
' -> Bitmap Heap Scan on patient (cost=81.08..18304.62 rows=1469 width=404)'
' Filter: ((patientname)::text ~~ ''BILL%, %''::text)'
' -> Bitmap Index Scan on ix_patientname (cost=0.00..81.08 rows=7347 width=0)'
' Index Cond: (((patientname)::text >= ''BILL''::character varying) AND ((patientname)::text < ''BILM''::character varying))'
' -> Unique (cost=18381.90..18418.62 rows=1469 width=404)'
' -> Sort (cost=18381.90..18385.57 rows=1469 width=404)'
' Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate'
' -> Bitmap Heap Scan on patient (cost=81.08..18304.62 rows=1469 width=404)'
' Filter: ((patientname)::text ~~ ''BILL%, %''::text)'
' -> Bitmap Index Scan on ix_patientname (cost=0.00..81.08 rows=7347 width=0)'
' Index Cond: (((patientname)::text >= ''BILL''::character varying) AND ((patientname)::text < ''BILM''::character varying))'
However the same
query on AIX (4 1.5Ghz processors, 60GB filesystem, 4GB Ram, postgres 8.1.2) it
takes like 5 secs because the query plan just uses sequentials
scans
Limit
(cost=100054251.96..100054253.41 rows=58 width=161)
-> Unique (cost=100054251.96..100054253.41 rows=58 width=161)
-> Sort (cost=100054251.96..100054252.11 rows=58 width=161)
Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate
-> Seq Scan on patient (cost=100000000.00..100054250.26 rows=58 width=161)
Filter: ((patientname)::text ~~ 'SMITH%, NA%'::text)
-> Unique (cost=100054251.96..100054253.41 rows=58 width=161)
-> Sort (cost=100054251.96..100054252.11 rows=58 width=161)
Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate
-> Seq Scan on patient (cost=100000000.00..100054250.26 rows=58 width=161)
Filter: ((patientname)::text ~~ 'SMITH%, NA%'::text)
Why is postgres
using a sequential scan and not the index what parameters do I need to
adjust
thanks
Tim
Jones
Optio
Software
