Re: Any better plan for this query?..

On 5/19/09, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.

you're probably right, but I don't like either when solution become so
complicated - PG has a so elegant way to execute a prepared query!

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

Good point! I may confirm only at least at the beginning all fields
are fully filled within a database. Will test both engines with
VARCHAR next time to be sure it's not an issue.

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

at least for PG there was no difference if I remember well.
However, before when I tested on the real network I finished by change
completely my code to reduce a network traffic (initially I've used
cursors), and finally PG traffic was lower or similar to MySQL, it was
an interesting stuff too :-)

Rgds,
-Dimitri

>
> merlin
>