Message-ID: <42FFE17A.6010208@arbash-meinel.com>
Date: Sun, 14 Aug 2005 19:27:38 -0500
From: John Arbash Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?St=E9phane_COEZ?= <scoez@harrysoftware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance pb vs SQLServer.
References: <20050811131911.3175A1C0031B@mwinf0401.wanadoo.fr>
In-Reply-To: <20050811131911.3175A1C0031B@mwinf0401.wanadoo.fr>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigD250D4B21DC4E3093825C750"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigD250D4B21DC4E3093825C750
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

St=E9phane COEZ wrote:

>Hi,
>
>I have a perfomance issue :
>
>I run PG (8.0.3) and SQLServer2000 on a Windows2000 Server (P4 1,5Ghz 51=
2Mo)
>I have a table (3200000 rows) and I run this single query :
>
>select cod from mytable group by cod
>I have an index on cod (char(4) - 88 different values)
>
>PG =3D ~ 20 sec.
>SQLServer =3D < 8 sec
>
>
>the explain is :
>
>HashAggregate  (cost=3D64410.09..64410.09 rows=3D55 width=3D8)
>  ->  Seq Scan on mytable  (cost=3D0.00..56325.27 rows=3D3233927 width=3D=
8)
>
>
>if I switch to "enable_hashagg =3D false" (just for a try...)
>the planner will choose my index :
>
>Group  (cost=3D0.00..76514.01 rows=3D55 width=3D8)
>  ->  Index Scan using myindex on mytable  (cost=3D0.00..68429.20 rows=3D=
3233927
>width=3D8)
>
>but performance will be comparable to previous test.
>
>So with or without using Index I have the same result.
> =20
>

My guess is that this is part of a larger query. There isn't really much
you can do. If you want all 3.2M rows, then you have to wait for them to
be pulled in.

What you generally can do for performance, is to restructure things, so
that you *don't* have to touch all 3.2M rows.
If you are just trying to determine what the unique entries are for cod,
you probably are better off doing some normalization, and keeping a
separate table of cod values.

I'm guessing the reason your query is faster with SQLServer is because
of how postgres handles MVCC. Basically, it still has to fetch the main
page to determine if a row exists. While SQL server doesn't do MVCC, so
it can just look things up in the index.

You might also try a different query, something like:

SELECT DISTINCT cod FROM mytable ORDER BY cod GROUP BY cod;
(You may or may not want order by, or group by, try the different
combinations.)
It might be possible to have the planner realize that all you want is
unique rows, just doing a group by doesn't give you that.

John
=3D:->

>
>Thanks for help.
>=20
>St=E9phane COEZ
>
>
>
>
>---------------------------(end of broadcast)---------------------------=

>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
> =20
>


--------------enigD250D4B21DC4E3093825C750
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC/+F6JdeBCYSNAAMRAjKFAJ9N79+cvK394sIPq4gIvwfFu8x2XgCaAwnm
QJu7OTqD6J+QWxIeIEj0jvk=
=I7DZ
-----END PGP SIGNATURE-----

--------------enigD250D4B21DC4E3093825C750--