From: John A Meinel <john@arbash-meinel.com>
To: Mahesh Shinde <mahesh.shinde@codecindia.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Data Selection Slow From VB 6.0
Date: Wed, 17 Aug 2005 10:18:35 -0500
Message-ID: <4303554B.2020500@arbash-meinel.com> (raw)
In-Reply-To: <009501c5a311$3706ef20$dd0aa8c0@codecindia.com>
References: <009501c5a311$3706ef20$dd0aa8c0@codecindia.com>
Mahesh Shinde wrote:
> Hi
...
> To connect To the PostgreSQL I am using **PostgreSQL Win32 ODBC and
> OLEDB client drivers 1.0.0.2**
>
...
> Since in the database I have one Major table that Debtor table which
> is master table and having around 55 lac records. I have set debtorId as
> a primary key having index on it.I am developing a search screen to
> search a specific debtor info using this table.
>
> 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 the
> database or not using ODBC. Where as when Explian the query on the
> database
> Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01
> rows=2 width=143)
> Index Cond: ((debtorid)::text = '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
=:->
>
> Query for the search criteria is
> *select * from tbmstdebtordetails where debtorid ='234'*
>
> 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
> *select * from tbmstdebtordetails where debtorid like '234%'*
>
> Explain output on the database
> Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01
> rows=2 width=143)
> Index Cond: ((debtorid)::text = '234%'::text)
> Thanks & regards,
> Mahesh Shinde
> ------------------------------------------------------------------
> Codec Communications (I) Pvt. Ltd.
> PUNE (INDIA)
> T # 91-20-24221460/70(Ext 43)
> Desk No. 25143
> Email – mahesh.shinde@codecindia.com <mailto:mahesh.shinde@codecindia.com>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, mahesh.shinde@codecindia.com, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Data Selection Slow From VB 6.0
In-Reply-To: <4303554B.2020500@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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