Re: Any better plan for this query?..

On 5/19/09, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, May 18, 2009 at 6:32 PM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>> Thanks Dave for correction, but I'm also curious where the time is
>> wasted in this case?..
>>
>> 0.84ms is displayed by "psql" once the result output is printed, and I
>> got similar time within my client (using libpq) which is not printing
>> any output..
>
> Using libpq? What is the exact method you are using to execute
> queries...PQexec?

exactly

> If you are preparing queries against libpq, the
> best way to execute queries is via PQexecPrepared.

the query is *once* prepared via PQexec,
then it's looping with "execute" via PQexec.
Why PQexecPrepared will be better in my case?..

> Also, it's
> interesting to see if you can get any benefit from asynchronous
> queries (PQsendPrepared), but this might involve more changes to your
> application than you are willing to make.
>
> Another note: I would like to point out again that there are possible
> negative side effects in using char(n) vs. varchar(n) that IIRC do not
> exist in mysql. When you repeat your test I strongly advise switching
> to varchar.

if it's true for any case, why not just replace CHAR implementation by
VARCHAR directly within PG code?..

>
> Another question: how exactly are you connecting to the database?
> local machine? if so, domain socket or tcp/ip?

local TCP/IP, same as MySQL

> What are you doing
> with the results...immediately discarding?

from PQ side they immediately discarded once all rows are fetched

>
> One last thing: when you get access to the server, can you run a
> custom format query test from pgbench and compare the results to your
> test similarly configured (same number of backends, etc) in terms of
> tps?

I'll try

Rgds,
-Dimitri