X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D0ABB9DCB3C for ; Thu, 19 Jan 2006 18:20:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22427-04 for ; Thu, 19 Jan 2006 18:20:27 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 639599DCB31 for ; Thu, 19 Jan 2006 18:20:23 -0400 (AST) Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by svr4.postgresql.org (Postfix) with ESMTP id E021E5AF09D for ; Thu, 19 Jan 2006 22:20:25 +0000 (GMT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C61D46.8A37A33B" Subject: query plans different for 8.1 on windows and aix Date: Thu, 19 Jan 2006 17:20:22 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D3033ACE@mail.optiosoftware.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: query plans different for 8.1 on windows and aix Thread-Index: AcYdRooBCWr7I2eSQmipOnI40lCipg== From: "Tim Jones" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[AWL=-0.001, HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/330 X-Sequence-Number: 16808 This is a multi-part message in MIME format. ------_=_NextPart_001_01C61D46.8A37A33B Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, =20 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=20 =20 'Limit (cost=3D18381.90..18384.40 rows=3D100 width=3D404)' ' -> Unique (cost=3D18381.90..18418.62 rows=3D1469 width=3D404)' ' -> Sort (cost=3D18381.90..18385.57 rows=3D1469 width=3D404)' ' Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate' ' -> Bitmap Heap Scan on patient (cost=3D81.08..18304.62 rows=3D1469 width=3D404)' ' Filter: ((patientname)::text ~~ ''BILL%, %''::text)' ' -> Bitmap Index Scan on ix_patientname (cost=3D0.00..81.08 rows=3D7347 width=3D0)' ' Index Cond: (((patientname)::text >=3D ''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 =20 Limit (cost=3D100054251.96..100054253.41 rows=3D58 width=3D161) -> Unique (cost=3D100054251.96..100054253.41 rows=3D58 width=3D161) -> Sort (cost=3D100054251.96..100054252.11 rows=3D58 = width=3D161) Sort Key: patientname, patientidentifier, patientvipindicator, patientconfidentiality, patientmrn, patientfacility, patientssn, patientsex, patientbirthdate -> Seq Scan on patient = (cost=3D100000000.00..100054250.26 rows=3D58 width=3D161) Filter: ((patientname)::text ~~ 'SMITH%, NA%'::text) Why is postgres using a sequential scan and not the index what parameters do I need to adjust =20 thanks Tim Jones Optio Software ------_=_NextPart_001_01C61D46.8A37A33B Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi,
 
  = I am trying=20 to perform the following type of query  'select patientname = ... from=20 patient were patientname LIKE 'JONES%, %' order by patientname asc limit = 100'.=20 There about 1.4 million rows in the table. On my windows machine (2GB = Ram ,3Ghz,=20 Windows XP, 120GB Hard Drive, postgres 8.1beta4) it takes about 150 = millisecs=20 and the query plan is
 
   =20 'Limit  (cost=3D18381.90..18384.40 rows=3D100 = width=3D404)'
' =20 ->  Unique  (cost=3D18381.90..18418.62 rows=3D1469=20 width=3D404)'
'        ->  = Sort  (cost=3D18381.90..18385.57 rows=3D1469=20 width=3D404)'
'         &= nbsp;   =20 Sort Key: patientname, patientidentifier, patientvipindicator,=20 patientconfidentiality, patientmrn, patientfacility, patientssn, = patientsex,=20 patientbirthdate'
'        &nb= sp;    =20 ->  Bitmap Heap Scan on patient  (cost=3D81.08..18304.62 = rows=3D1469=20 width=3D404)'
'         &= nbsp;         =20 Filter: ((patientname)::text ~~ ''BILL%,=20 %''::text)'
'         &nb= sp;         =20 ->  Bitmap Index Scan on ix_patientname  = (cost=3D0.00..81.08=20 rows=3D7347=20 width=3D0)'
'         &nb= sp;           &nbs= p;   =20 Index Cond: (((patientname)::text >=3D ''BILL''::character varying) = AND=20 ((patientname)::text < ''BILM''::character = varying))'
However the same=20 query on AIX (4 1.5Ghz processors, 60GB filesystem, 4GB Ram, postgres = 8.1.2) it=20 takes like 5 secs because the query plan just uses sequentials=20 scans
 
Limit =20 (cost=3D100054251.96..100054253.41 rows=3D58 = width=3D161)
   -> =20 Unique  (cost=3D100054251.96..100054253.41 rows=3D58=20 width=3D161)
         = -> =20 Sort  (cost=3D100054251.96..100054252.11 rows=3D58=20 width=3D161)
         &nb= sp;    =20 Sort Key: patientname, patientidentifier, patientvipindicator,=20 patientconfidentiality, patientmrn, patientfacility, patientssn, = patientsex,=20 patientbirthdate
         = ;     =20 ->  Seq Scan on patient  (cost=3D100000000.00..100054250.26 = rows=3D58=20 width=3D161)
         &nb= sp;          =20 Filter: ((patientname)::text ~~ 'SMITH%, = NA%'::text)
Why is = postgres=20 using a sequential scan and not the index what parameters do I need to=20 adjust
 
thanks
Tim=20 Jones
Optio=20 Software
------_=_NextPart_001_01C61D46.8A37A33B--