Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TH9XS-0000ae-3a for pgsql-performance@postgresql.org; Thu, 27 Sep 2012 08:33:42 +0000 Received: from snt0-omc4-s39.snt0.hotmail.com ([65.54.51.90]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TH9XP-0003jG-Mt for pgsql-performance@postgresql.org; Thu, 27 Sep 2012 08:33:41 +0000 Received: from SNT111-W32 ([65.55.90.201]) by snt0-omc4-s39.snt0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 27 Sep 2012 01:33:33 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_de3a420c-3420-4574-976b-c6ab77760404_" X-Originating-IP: [92.103.148.121] From: Undertaker Rude To: CC: Subject: Re: Same query doing slow then quick Date: Thu, 27 Sep 2012 10:33:33 +0200 Importance: Normal In-Reply-To: References: , <506307E2.4060504@ulb.ac.be>, <1348668859243-5725505.post@n5.nabble.com>, <50630E6F.5040605@ulb.ac.be>, , <506316B1.70900@ulb.ac.be>, , , , , , MIME-Version: 1.0 X-OriginalArrivalTime: 27 Sep 2012 08:33:33.0863 (UTC) FILETIME=[C8025B70:01CD9C8A] X-Pg-Spam-Score: -1.7 (-) X-Archive-Number: 201209/153 X-Sequence-Number: 47899 --_de3a420c-3420-4574-976b-c6ab77760404_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Oh ok. But what is this command doing ? i'm gonna runn it today. I'll keep = you posted. Here is some EXPLAIN ANALYZE from the querys : Nested Loop (cost=3D0.00..353722.89 rows=3D124893 width=3D16) (actual time= =3D261158.061..10304193.501 rows=3D99 loops=3D1) Join Filter: ((t2."X" >= =3D (t1.x_min)::double precision) AND (t2."X" <=3D (t1.x_max)::double preci= sion) AND (t2."Y" >=3D (t1.y_min)::double precision) AND (t2."Y" <=3D (t1.y= _max)::double precision)) -> Seq Scan on gps_22 t1 (cost=3D0.00..3431.80= rows=3D177480 width=3D44) (actual time=3D0.036..1399.621 rows=3D177480 loo= ps=3D1) -> Materialize (cost=3D0.00..20572.83 rows=3D57 width=3D20) (act= ual time=3D0.012..10.274 rows=3D2924 loops=3D177480) -> Seq Scan on= adresses_22 t2 (cost=3D0.00..20572.55 rows=3D57 width=3D20) (actual time= =3D1570.240..1726.376 rows=3D2924 loops=3D1) Filter: ((id_mail= le_200m)::text =3D '0'::text)Total runtime: 10304211.648 ms Nested Loop (cost=3D0.00..88186069.17 rows=3D33397899 width=3D16) (actual = time=3D3060.373..3060.373 rows=3D0 loops=3D1) Join Filter: ((t2."X" >=3D (= t1.x_min)::double precision) AND (t2."X" <=3D (t1.x_max)::double precision)= AND (t2."Y" >=3D (t1.y_min)::double precision) AND (t2."Y" <=3D (t1.y_max)= ::double precision)) -> Seq Scan on gps_31 t1 (cost=3D0.00..3096.38 rows= =3D161738 width=3D44) (actual time=3D4.612..442.935 rows=3D161738 loops=3D1= ) -> Materialize (cost=3D0.00..12562.25 rows=3D16726 width=3D20) (actual= time=3D0.012..0.012 rows=3D0 loops=3D161738) -> Seq Scan on adress= es_31 t2 (cost=3D0.00..12478.62 rows=3D16726 width=3D20) (actual time=3D15= 04.082..1504.082 rows=3D0 loops=3D1) Filter: ((id_maille_200m)= ::text =3D '0'::text)Total runtime: 3060.469 ms Nested Loop (cost=3D0.00..84287659.70 rows=3D31920943 width=3D64) (actual = time=3D220198.891..32665395.631 rows=3D21409 loops=3D1) Join Filter: ((t2.= "X" >=3D (t1.x_min)::double precision) AND (t2."X" <=3D (t1.x_max)::double = precision) AND (t2."Y" >=3D (t1.y_min)::double precision) AND (t2."Y" <=3D = (t1.y_max)::double precision)) -> Seq Scan on gps_67 t1 (cost=3D0.00..23= 50.55 rows=3D121555 width=3D44) (actual time=3D0.038..1570.994 rows=3D12155= 5 loops=3D1) -> Materialize (cost=3D0.00..14072.09 rows=3D21271 width=3D= 20) (actual time=3D0.001..34.394 rows=3D22540 loops=3D121555) -> Se= q Scan on adresses_67 t2 (cost=3D0.00..13965.74 rows=3D21271 width=3D20) (= actual time=3D0.032..1283.087 rows=3D22540 loops=3D1) Filter: = ((id_maille_200m)::text =3D '0'::text)Total runtime: 32665478.631 ms Rude - Last Territory Ou =E9couter ?http://www.deezer.com/fr/music/last-territory/the-last-hope-3= 617781 (Post-apocalyptic Metal)http://www.deezer.com/fr/music/rude-un= dertaker (Pop-Rock) Ou acheter ?La Fnachttp://recherche.fnac.com/fmia14622213/Last-Territory http://recherche.fnac.com/fmia14770622/Rude-Undertaker iTuneshttp://itunes.apple.com/us/artist/last-territory/id533857009?ign-mpt= =3Duo%3D4 > Date: Wed=2C 26 Sep 2012 19:30:30 +0300 > Subject: Re: [PERFORM] Same query doing slow then quick > From: lystor@gmail.com > To: FFW_Rude@hotmail.com > CC: pgsql-performance@postgresql.org >=20 > On 26 September 2012 19:09=2C FFW_Rude wrote: > > Could you explain what you are asking me to do because i don't really k= now > > what i'm doing... >=20 > postgresql-contrib packages contains pgbench tool on Ubuntu. >=20 > For example postgresql-contrib-9.1_9.1.3-2_i386.deb on Ubuntu 12.04 conta= ins: > /usr/lib/postgresql/9.1/bin/pgbench >=20 >=20 > > i don't have pgbench in it. It is still pgbench command not found... >=20 > You need to run pgbench as postgres user. > For example on CentOS: > # su - postgres > $ pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench >=20 >=20 > -- > With best regards=2C > Nikolay = --_de3a420c-3420-4574-976b-c6ab77760404_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Oh ok. But what is this command doing ? i'm gonna runn it today. I'll keep = you posted. Here is some EXPLAIN ANALYZE from the querys :


Nested Loop  =3B(cost=3D0.00..353722.89 rows=3D1= 24893 width=3D16) (actual time=3D261158.061..10304193.501 rows=3D99 loops= =3D1)
 =3B Join Filter: ((t2."X" >=3B=3D (t1.x_min)::double= precision) AND (t2."X" <=3B=3D (t1.x_max)::double precision) AND (t2."Y"= >=3B=3D (t1.y_min)::double precision) AND (t2."Y" <=3B=3D (t1.y_max)::= double precision))
 =3B ->=3B  =3BSeq Scan on gps_22 t1=  =3B(cost=3D0.00..3431.80 rows=3D177480 width=3D44) (actual time=3D0.0= 36..1399.621 rows=3D177480 loops=3D1)
 =3B ->=3B  =3BMa= terialize  =3B(cost=3D0.00..20572.83 rows=3D57 width=3D20) (actual time= =3D0.012..10.274 rows=3D2924 loops=3D177480)
 =3B  =3B &n= bsp=3B  =3B ->=3B  =3BSeq Scan on adresses_22 t2  =3B(cost=3D= 0.00..20572.55 rows=3D57 width=3D20) (actual time=3D1570.240..1726.376 rows= =3D2924 loops=3D1)
 =3B  =3B  =3B  =3B  =3B &= nbsp=3B  =3B Filter: ((id_maille_200m)::text =3D '0'::text)
T= otal runtime: 10304211.648 ms


Nested Loop  =3B(cost=3D0.00..88186069.17 rows=3D33397899 width= =3D16) (actual time=3D3060.373..3060.373 rows=3D0 loops=3D1)
&nbs= p=3B Join Filter: ((t2."X" >=3B=3D (t1.x_min)::double precision) AND (t2.= "X" <=3B=3D (t1.x_max)::double precision) AND (t2."Y" >=3B=3D (t1.y_min= )::double precision) AND (t2."Y" <=3B=3D (t1.y_max)::double precision))
 =3B ->=3B  =3BSeq Scan on gps_31 t1  =3B(cost=3D0.0= 0..3096.38 rows=3D161738 width=3D44) (actual time=3D4.612..442.935 rows=3D1= 61738 loops=3D1)
 =3B ->=3B  =3BMaterialize  =3B(co= st=3D0.00..12562.25 rows=3D16726 width=3D20) (actual time=3D0.012..0.012 ro= ws=3D0 loops=3D161738)
 =3B  =3B  =3B  =3B ->= =3B  =3BSeq Scan on adresses_31 t2  =3B(cost=3D0.00..12478.62 rows= =3D16726 width=3D20) (actual time=3D1504.082..1504.082 rows=3D0 loops=3D1)<= /div>
 =3B  =3B  =3B  =3B  =3B  =3B  =3B Fi= lter: ((id_maille_200m)::text =3D '0'::text)
Total runtime: 3060.= 469 ms

Nested Loop  =3B(cost=3D0.00= ..84287659.70 rows=3D31920943 width=3D64) (actual time=3D220198.891..326653= 95.631 rows=3D21409 loops=3D1)
 =3B Join Filter: ((t2."X" >= =3B=3D (t1.x_min)::double precision) AND (t2."X" <=3B=3D (t1.x_max)::doub= le precision) AND (t2."Y" >=3B=3D (t1.y_min)::double precision) AND (t2."= Y" <=3B=3D (t1.y_max)::double precision))
 =3B ->=3B &nbs= p=3BSeq Scan on gps_67 t1  =3B(cost=3D0.00..2350.55 rows=3D121555 width= =3D44) (actual time=3D0.038..1570.994 rows=3D121555 loops=3D1)
&n= bsp=3B ->=3B  =3BMaterialize  =3B(cost=3D0.00..14072.09 rows=3D21= 271 width=3D20) (actual time=3D0.001..34.394 rows=3D22540 loops=3D121555)
 =3B  =3B  =3B  =3B ->=3B  =3BSeq Scan on ad= resses_67 t2  =3B(cost=3D0.00..13965.74 rows=3D21271 width=3D20) (actua= l time=3D0.032..1283.087 rows=3D22540 loops=3D1)
 =3B  = =3B  =3B  =3B  =3B  =3B  =3B Filter: ((id_maille_200m):= :text =3D '0'::text)
Total runtime: 32665478.631 ms


<= span class=3D"ecxApple-style-span" style=3D"font-size:10pt">Rude - Last Ter= ritory

=
Ou =E9couter ?<= /b>
http://www.deezer.com/fr/music/last-terr= itory/the-last-hope-3617781  =3B =3B  =3B  =3B(Post-apocalyptic Metal)=

=
Ou acheter ?
La Fnac

iTunes


>=3B Date: Wed= =2C 26 Sep 2012 19:30:30 +0300
>=3B Subject: Re: [PERFORM] Same query = doing slow then quick
>=3B From: lystor@gmail.com
>=3B To: FFW_Ru= de@hotmail.com
>=3B CC: pgsql-performance@postgresql.org
>=3B >=3B On 26 September 2012 19:09=2C FFW_Rude <=3BFFW_Rude@hotmail.com&g= t=3B wrote:
>=3B >=3B Could you explain what you are asking me to do= because i don't really know
>=3B >=3B what i'm doing...
>=3B <= br>>=3B postgresql-contrib packages contains pgbench tool on Ubuntu.
&= gt=3B
>=3B For example postgresql-contrib-9.1_9.1.3-2_i386.deb on Ubu= ntu 12.04 contains:
>=3B /usr/lib/postgresql/9.1/bin/pgbench
>=3B=
>=3B
>=3B >=3B i don't have pgbench in it. It is still pgben= ch command not found...
>=3B
>=3B You need to run pgbench as pos= tgres user.
>=3B For example on CentOS:
>=3B # su - postgres
&= gt=3B $ pgbench -i pgbench &=3B&=3B pgbench -c 10 -t 10000 pgbench>=3B
>=3B
>=3B --
>=3B With best regards=2C
>=3B N= ikolay
= --_de3a420c-3420-4574-976b-c6ab77760404_--