pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery plans different for 8.1 on windows and aix
2+ messages / 2 participants
[nested] [flat]
* query plans different for 8.1 on windows and aix
@ 2006-01-19 22:20 Tim Jones <TJones@optio.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tim Jones @ 2006-01-19 22:20 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query plans different for 8.1 on windows and aix
@ 2006-01-19 22:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Jones <TJones@optio.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-19 22:27 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: pgsql-performance
"Tim Jones" <TJones@optio.com> writes:
> Why is postgres using a sequential scan and not the index what
> parameters do I need to adjust
You probably initialized the AIX database in a non-C locale.
See the manual concerning LIKE index optimizations.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-19 22:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-19 22:20 query plans different for 8.1 on windows and aix Tim Jones <TJones@optio.com>
2006-01-19 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox