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))'

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)

Why is postgres using a sequential scan and not the index what
parameters do I need to adjust

thanks
Tim Jones
Optio Software

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 2 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJan 19, '06 at 10:20p
activeJan 19, '06 at 10:27p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Tim Jones: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase