Message-ID: <4303554B.2020500@arbash-meinel.com>
Date: Wed, 17 Aug 2005 10:18:35 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: Mahesh Shinde <mahesh.shinde@codecindia.com>
Cc: pgsql-performance@postgresql.org, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Data Selection Slow From VB 6.0
References: <009501c5a311$3706ef20$dd0aa8c0@codecindia.com>
In-Reply-To: <009501c5a311$3706ef20$dd0aa8c0@codecindia.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig620020274E7B6ABB6664657C"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig620020274E7B6ABB6664657C
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Mahesh Shinde wrote:
> Hi
=2E..

>     To connect To the PostgreSQL I am using **PostgreSQL Win32 ODBC and=

> OLEDB client drivers 1.0.0.2**
> =20

=2E..

>     Since in the database I have one Major table that Debtor table whic=
h
> is master table and having around 55 lac records. I have set debtorId a=
s
> a primary key having index on it.I am developing a search screen to
> search a specific debtor info using this table.
> =20
> When I fire a query to search a debtor id,  it took around 5 seconds to=

> return an answer for a query whether entered debtor id is present in th=
e
> database or not using ODBC. Where as when Explian  the query on the
> database
>  Index Scan using tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6=
=2E01
> rows=3D2 width=3D143)
>    Index Cond: ((debtorid)::text =3D '234'::text)

Are you checking this from the VB App? Or just going onto the server and
running psql? (I'm guessing there is some way to run a flat query using
VB. In which case you can just have the query run EXPLAIN ANALYZE, the
return value is just the text, one line after another.)

What I'm thinking is that it might be a locale/encoding issue.
What is the encoding of your database? And what is the default locale
and the locale that you are connecting as?

Can you give us the "EXPLAIN ANALYZE" output so that we can see if the
planner is doing what it thinks it is?

It certainly sounds like either it is always doing a sequential scan, or
something else is going on. 5 sec is a really long time for the type of
query you are doing.

Oh, and can you run the win32 psql client to see if it might be ODBC
which is causing the problem?

John
=3D:->


>=20
> Query for the search criteria is
>  *select * from tbmstdebtordetails where debtorid =3D'234'*
> =20
>  Where as when I am using a like query to search a record starting with=

> debtor id having a characters then it took around 10-15 sec to return a=

> record set having records.
> query is=20
> *select * from tbmstdebtordetails where debtorid like '234%'*
> =20
> Explain output on the database
>  Index Scan using tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6=
=2E01
> rows=3D2 width=3D143)
>    Index Cond: ((debtorid)::text =3D '234%'::text)
> Thanks & regards,
> Mahesh Shinde
> ------------------------------------------------------------------
> Codec Communications (I) Pvt. Ltd.
> PUNE (INDIA)
> T # 91-20-24221460/70(Ext 43)
> Desk No. 25143
> Email =E2=80=93 mahesh.shinde@codecindia.com <mailto:mahesh.shinde@code=
cindia.com>


--------------enig620020274E7B6ABB6664657C
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDA1VLJdeBCYSNAAMRAswzAKCd7lERmivZb8CinkVh8ognvDjoqACdETdw
EY5dlmeNaDewSiibDeK+vFQ=
=hTR3
-----END PGP SIGNATURE-----

--------------enig620020274E7B6ABB6664657C--