Re: Any better plan for this query?..

On Tue, May 19, 2009 at 11:53 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
> 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?..

It can be better or worse (usually better). the parameters are
separated from the query string. Regardless of performance, the
parametrized interfaces are superior for any queries taking arguments
and should be used when possible.

>> 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?..

First, let me explain the difference. char(n) is padded out to 'n' on
disk and when returned. despite this, the length is still stored so
there is no real advantage to using the char(n) type except that the
returned string is of a guaranteed length. mysql, at least the
particular version and storage engine that I am logged into right now,
does not do this for char(n). In other words, select cast('abc' as
char(50)) returns a string of 50 chars on pgsql and 3 chars on mysql.
I will leave it as an exercise to the reader to figure out whom is
following the standard. pg's handling of the situation is not
necessarily optimal, but we just tell everyone to quit using 'char(n)'
type.

Unless for example your 'NOTE' column is mostly full or mostly null,
your query is not fair because postgres has to both store and return a
proportionally greater amount of data. This makes the comparison
hardly apples to apples. This stuff counts when we are measuring at
microsecond level.

>> 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

would be curious to see if you get different results from domain socket.

merlin