Re: [QUESTIONS] MySQL benchmark page - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: [QUESTIONS] MySQL benchmark page
Date
Msg-id 199803161428.JAA05741@candle.pha.pa.us
Whole thread Raw
In response to Re: [QUESTIONS] MySQL benchmark page  ("Luuk de Boer" <luuk_de_boer@pi.net>)
List pgsql-hackers
> I have looked into crash-me and screened the items you mentioned.
> Here is the result:
>
> about the large objects. I looked in the manual of postgres and
> couldn't find that fast anything about large objects.
> blobs is a type where you can put binary or other big things in.

We call them large objects.

>
> first the concatenation with ||
> the test is select 'abc' || 'def'
> and that's not working in postgres. In other db's it's working.

Looks like we have a type problem here.  That type system again.

>
> function left:
> the test is select left('abcd',2)
> looked in the manual of postgres and couldn't find the function left.
> this isn't working and I tried some other ways but couldn't find any
> working query.

Yep, we don't have it.  We have lots of them in the oracle_compat
module, but not that one.

> function current_date:
> the test is select current_date()
> that's postgres not supporting only select current_date (without ()).
> that's also the case with current_time, current_timestamp. I will make
> patch so it's tested both ways.

Does the standard say we need those parens?

> Function IN on strings in SELECT:
> the test is: select 'monty' in ('david','monty','allan')
> and that's not working. it gives: using an explicit cast

Darn type system again.

> Function = ALL:
> query: select a from crash_me where b =all (select b from crash_me)
> yes
> so it's supported

Cool.

>
> Update with sub select:
> query: create table crash_q (a integer,b char(10))
> query: insert into crash_q values(1,'c')
> query: update crash_q set crash_q.b= (select b from crash_me
> where crash_q.a = crash_me.a)
> Got error from query: 'update crash_q
> set crash_q.b= (select b from crash_me whe re crash_q.a = crash_me.a)'
> ERROR:  parser: parse error at or near "=" query: drop table crash_q
> no
> I think this test shows that it isn't possible todo an update with a
> sub select

Yes, we do not support subqueries in the target list.  We do support
subqueries in the WHERE clause of all queries, including UPDATE, and I
mistakenly thought that was the item.   Adding this feature is on our
TODO list, because subqueries legally should be allowed in any target
list, including UPDATE and SELECT.

>
> case insensitive compare:
> query: select b from crash_me where b = 'A'
> query: select b from crash_me where b = 'A' didn't return any
> result
> no
> (field b in table crash_me has a value of 'a'.)

We use ~* for this.  How do you control case-sensitivity?  Are all
strings compared for lower and upper case.  We also have many of those
math functions, but they are operations, not names like exp().  Try the
new psql \df and \do commands.

Also, it mentioned that MySQL ignores the CREATE INDEX operation.  What
does that mean?  Does MySQL only support one index on a table?

> the transaction are still not inserted in the crash-me test because
> monty and I didn't have time to insert it. But what I mentioned
> before if you would like to see some other tests in the crash-me test
> you can add them by your self and send us the patch. We will add it
> to the release of crash-me.
>
> I am not sure the results of postgres in the crash-me page is from
> the final release of postgres. Because I have so much problems with
> postgres it's at this moment hard to test. Further it's taking a very
> lot of time to test the postgres db because it's eating my memory.
> When I test the limit of the number of joins I can do it's eating my
> memory till there is no memory left anymore. And that's a lot (96 mb
> real + 470mb swap).
>
> Greetz...
>
> Luuk
> |-----------------------------------------------------------------------|
> |  Luuk de Boer -> <luuk@pi.net>                                      |
> |  When you want to go fast .....                                       |
> |  Drive a cobra on the road and take a T3 line for internet ...... :-) |
> |  http://www.luuk.com/  -> The AC Cobra site                           |
> |-----------------------------------------------------------------------|
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

pgsql-hackers by date:

Previous
From: Andrew Martin
Date:
Subject: Re: [HACKERS] Re: [PORTS] Port Bug Report: Grant select to other user removes owner select permission
Next
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] Re: [PORTS] Port Bug Report: Grant select to other user