X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8D30B5694D for ; Wed, 16 Mar 2005 13:50:55 +0000 (GMT) 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 77575-08 for ; Wed, 16 Mar 2005 13:50:46 +0000 (GMT) Received: from mail.eulerhermes.pl (mail.hermes-kredit.pl [217.153.119.83]) by svr1.postgresql.org (Postfix) with ESMTP id 3095A56721 for ; Wed, 16 Mar 2005 13:50:45 +0000 (GMT) Received: from [192.168.49.9] (helo=xterm.podsiec) by mail.eulerhermes.pl with esmtpa (Exim 4.42) id 1DBYlT-0006OE-3n for pgsql-admin@postgresql.org; Wed, 16 Mar 2005 14:40:47 +0100 From: Marcin Giedz Organization: Euler Hermes To: pgsql-admin@postgresql.org Subject: Re: Performance problem... Date: Wed, 16 Mar 2005 14:46:26 +0100 User-Agent: KMail/1.7.1 References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <200503151717.36288.marcin.giedz@eulerhermes.pl> <1110906024.28555.146.camel@state.g2switchworks.com> In-Reply-To: <1110906024.28555.146.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200503161446.26640.marcin.giedz@eulerhermes.pl> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.124 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/238 X-Sequence-Number: 16736 Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisa=C5=82: OK now I know I mys query lasts so long: SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii, t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer, t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.numer, t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer, t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.adres AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma =3D n.IdPodmiot AND t1.IdTypNazwa =3D 153 AND t1.Historia =3D 0 LEFT JOIN firmy.numer AS t3 ON t3.IdPodmiot =3D t1.IdFirma AND t3.IdTypNumer =3D 156 AND t3.Historia =3D 0= LEFT JOIN firmy.numer AS t4 ON t4.IdPodmiot =3D t1.IdFirma AND t4.IdTypNumer =3D= 160 AND t4.Historia =3D 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot =3D t1.Id= =46irma AND t5.IdTypNumer =3D 155 AND t5.Historia =3D 0 LEFT JOIN firmy.numer AS t6= ON t6.IdPodmiot =3D t1.IdFirma AND t6.IdTypNumer =3D 627 AND t6.Historia =3D 0= LEFT JOIN firmy.numer AS t7 ON t7.IdPodmiot =3D t1.IdFirma AND t7.IdTypNumer =3D= 848 AND t7.Historia =3D 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot =3D t1.Id= =46irma AND t8.IdTypNumer =3D 763 AND t8.Historia =3D 0 LEFT JOIN firmy.numer AS t9= ON t9.IdPodmiot =3D t1.IdFirma AND t9.IdTypNumer =3D 762 AND t9.Historia =3D 0= LEFT JOIN firmy.formaprawna AS t10 ON t10.IdFirma =3D t1.IdFirma LEFT JOIN slowniki.tslownik AS t11 ON t11.idslownik =3D t10.IdTypFormaPrawna AND t11.idjezyktyp =3D 2 WHERE n.Miasto ILIKE 'warszawa%' LIMIT 25 =2E..because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to sear= ch=20 several records ..... without DISTINCT (of course a few doubled records=20 occured) but query tooks 10ms ;). On the other hand doubled records are=20 annoying ..... is it possible to avoid these without changing data in table= s?=20 Maybe some other query? BR, Marcin > On Tue, 2005-03-15 at 10:17, Marcin Giedz wrote: > > Dnia wtorek, 15 marca 2005 17:08, Scott Marlowe napisa=C5=82: > > > On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote: > > > > Dnia poniedzia=C5=82ek, 14 marca 2005 19:32, Scott Marlowe napisa= =C5=82: > > > > > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote: > > > > > > Hello... > > > > > > > > > > > > > > > > > > Our company is going to change SQL engine from MySQL to PSQL. Of > > > > > > course some performance problems occured. Our server is Dual Xe= on > > > > > > 3.0GHz + 8GB RAM + RAID1(software - two 146GB SCSI 15k) for sql > > > > > > data + RAID1(software - two 146GB SCSI 15k) for pg_xlog. > > > > > > Postgres.conf parameters are as follows: > > > > > > > > > > > > max_connections =3D 150 > > > > > > shared_buffers =3D 50000 # min 16, at least > > > > > > max_connections*2, 8KB each work_mem =3D 2048 # min 64, > > > > > > size in KB > > > > > > > > > > 50,000 shared buffers may or may not be too much. Try it at > > > > > different sizes from 5,000 or so up to 50,000 and find the "knee"= =2E=20 > > > > > It's usually closer to 10,000 than 50,000, but ymmv... > > > > > > > > Playing with shared_buffers from 10000 to 50000 doesn't change > > > > anything in total time for this query :( But when I change work_mem= a > > > > little higher to 10000 total runtime decreases a little about 10% b= ut > > > > when I change random_page_cost to 0.2 (I know that almost all papers > > > > say it should be higher then 1.0) total runtime decreases almost 3 > > > > times and lasts about 900ms - earlier with random_page_cost=3D1.2 it > > > > took 2.7s. Is it possible to have random_page_cost on this value? > > > > > > IF random_page_cost needs to be that low, then it's likely that the > > > query planner is either getting bad statistics and making a poor > > > decision, or that you've got a corner case that it just can't figure > > > out. What does explain analyze say with > > > random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuum= ed > > > full lately? > > > > It cann't be possible - I've run vacuum full analyze - it didn't change > > anything ;) > > You might want to try adjusting these values to see if you can get the > query planner to choose the faster plan without dropping > random_page_cost to 0.2. I.e. give the query planner candy and flowers, > don't just bonk it on the head with a big stick and drag it back home... > > #cpu_tuple_cost =3D 0.01 # (same) > #cpu_index_tuple_cost =3D 0.001 # (same) > #cpu_operator_cost =3D 0.0025 # (same) > > Does explain analyze show a big difference in expected an actual rows > returned for any of the parts of the query plan? > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match