Re: Perfomance in comparism

Ralf Kramer napisał(a):
> Hi there,
>
> I am playing with some databases, in order find one that is more
> satisfying then MySQL.
> I have installed now Firebird, MySQL and Postgresql and executed some
> simple queries on a
> bigger table with approx 180.000 records. The results appear a bit
> strange to me:
>
> MySQL
> =======================Timer=======================
> time to execute is 1.85630297661 sec
> =======================/Timer=======================
>
> Firebird
> =======================Timer=======================
> time to execute is 0.131076097488 sec
> =======================/Timer=======================
>
> Postgres
> =======================Timer=======================
> time to execute is 13.9053928852 sec
> =======================/Timer=======================
>
> The query is simply:
> SELECT
> *
> FROM
> \"user\"
> WHERE
> lastname = 'Müller'
> AND
> gender = 'male'
>
> On the field lastname is an index: CREATE INDEX lastname_index ON "user"
> USING btree (lastname)
> The query is executed subsequently to a reboot, thus, no chaching
> features apply on this queries. But
> even when I run the query a second time I get this strange results.
>
> MySQL
> =======================Timer=======================
> time to execute is 0.0577750205994 sec
> =======================/Timer=======================
>
> start Firebird
> =======================Timer=======================
> time to execute is 0.00465512275696 sec
> =======================/Timer=======================
>
> Postgres
> =======================Timer=======================
> time to execute is 0.209079027176 sec
> =======================/Timer=======================
>
> I am absolutely new to Postgres and there is certainly one mistake in my
> test environment, but have no clue what this can be.
> Any help will appreciated ;-)

First you should make an index on "gender" field. After that do VACUUM
FULL ANALYZE. Also it would be better to define in the query all the
fields you would like to see instead of "*".

From my experience I know that PostgreSQL can be much faster than MySQL
- it is a mather of PG configuration, db structure and query definition.

ML