X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2DE85D1B737 for ; Fri, 18 Jun 2004 14:53:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06710-07 for ; Fri, 18 Jun 2004 17:53:16 +0000 (GMT) Received: from relay.snowman.net (snowman.net [66.93.83.236]) by svr1.postgresql.org (Postfix) with ESMTP id 0C022D1B503 for ; Fri, 18 Jun 2004 14:53:14 -0300 (ADT) Received: from ns.snowman.net (ns.snowman.net [10.10.0.2]) by relay.snowman.net (8.12.11/8.12.11/Debian-5) with ESMTP id i5IHrBvB025901; Fri, 18 Jun 2004 13:53:11 -0400 Received: from ns.snowman.net (localhost [127.0.0.1]) by ns.snowman.net (8.12.11/8.12.10/Debian-5) with ESMTP id i5IHrHhA019333; Fri, 18 Jun 2004 13:53:17 -0400 Received: (from sfrost@localhost) by ns.snowman.net (8.12.11/8.12.10/Debian-5) id i5IHrHs3019330; Fri, 18 Jun 2004 13:53:17 -0400 Date: Fri, 18 Jun 2004 13:53:17 -0400 From: Stephen Frost To: Tom Lane Cc: Gary Cowell , pgsql-performance@postgresql.org Subject: Re: Major differences between oracle and postgres performance - what can I do ? Message-ID: <20040618175317.GN11196@ns.snowman.net> Mail-Followup-To: Tom Lane , Gary Cowell , pgsql-performance@postgresql.org References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> <20412.1087570627@sss.pgh.pa.us> <20040618163003.GM11196@ns.snowman.net> <22514.1087578111@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="eaOnqiUIFHwTGLlS" Content-Disposition: inline In-Reply-To: <22514.1087578111@sss.pgh.pa.us> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.4.24ns.3.0 (i686) X-Uptime: 13:07:55 up 139 days, 12:13, 6 users, load average: 0.09, 0.11, 0.10 User-Agent: Mutt/1.5.5.1+cvs20040105i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/199 X-Sequence-Number: 7257 --eaOnqiUIFHwTGLlS Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > >> [... thinks for awhile ...] It seems possible that they may use sort > >> code that knows it is performing a DISTINCT operation and discards > >> duplicates on sight. Given that there are only 534 distinct values, > >> the sort would easily stay in memory if that were happening. >=20 > > Could this optimization be added to PostgreSQL? It sounds like a very > > reasonable thing to do. >=20 > That's what I was wondering about too. But first I'd like to get > some kind of reading on how effective it would be. If someone can > demonstrate that Oracle can do sort-and-drop-dups a lot faster than > it can do a straight sort of the same amount of input data, that > would be a strong indication that it's worth doing. At this point > we don't know if that's the source of their win or not. Alright, I did a couple tests, these are different systems with different hardware, but in the end I think the difference is clear: tsf=3D# explain analyze select distinct access_type_id from p_gen_dom_dedic= ated_swc_access ; QUER= Y PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 Unique (cost=3D321591.00..333205.56 rows=3D16 width=3D10) (actual time=3D= 32891.141..37420.429 rows=3D16 loops=3D1) -> Sort (cost=3D321591.00..327398.28 rows=3D2322912 width=3D10) (actua= l time=3D32891.137..35234.810 rows=3D2322912 loops=3D1) Sort Key: access_type_id -> Seq Scan on p_gen_dom_dedicated_swc_access (cost=3D0.00..5549= 2.12 rows=3D2322912 width=3D10) (actual time=3D0.013..3743.470 rows=3D23229= 12 loops=3D1) Total runtime: 37587.519 ms (5 rows) tsf=3D# explain analyze select access_type_id from p_gen_dom_dedicated_swc_= access order by access_type_id; QUERY P= LAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ----------------------------------------------------------------------- Sort (cost=3D321591.00..327398.28 rows=3D2322912 width=3D10) (actual time= =3D32926.696..35278.847 rows=3D2322912 loops=3D1) Sort Key: access_type_id=20=20=20=20=20 -> Seq Scan on p_gen_dom_dedicated_swc_access (cost=3D0.00..55492.12 r= ows=3D2322912 width=3D10) (actual time=3D0.014..3753.443 rows=3D2322912 loo= ps=3D1) Total runtime: 36737.628 ms=20=20=20=20 (4 rows)=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 So, about the same from postgres in each case. From Oracle: (select access_type_id from p_gen_dom_dedicated_swc_access order by access_= type_id) sauron:/home/sfrost> time sqlplus mci_vendor/mci @test.sql > /dev/null real 3m55.12s user 2m25.87s sys 0m10.59s (select distinct access_type_id from p_gen_dom_dedicated_swc_access) sauron:/home/sfrost> time sqlplus mci_vendor/mci @test.sql > /dev/null real 0m5.08s user 0m3.86s sys 0m0.95s All the queries were run multiple times, though there wasn't all that much difference in the times. Both systems are pretty speedy, but I tend to feel the Postgres box is faster in CPU/disk access time, which is probably why the Oracle system took 4 minutes to do what the Postgres systems does in 40 seconds. My only other concern is the Oracle system having to do the write I/O while the postgres one doesn't... I don't see an obvious way to get around that though, and I'm not sure if it'd really make *that* big of a difference. Stephen --eaOnqiUIFHwTGLlS Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFA0ywNrzgMPqB3kigRAgNjAJ4vatzGX8hkylQDWWMPnljmm9vuvwCfQrwt 7ljdjAI3YSV7qIxSaKh7xzU= =aOlw -----END PGP SIGNATURE----- --eaOnqiUIFHwTGLlS--