pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Undertaker Rude <ffw_rude@hotmail.com>
To: lystor@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Same query doing slow then quick
Date: Thu, 27 Sep 2012 10:33:33 +0200
Message-ID: <SNT111-W32B747BB89423F7197F456E1830@phx.gbl> (raw )
In-Reply-To: <CA+MedixsJbhjPGsKiWsp3ENjAnR3r3Ajw_nVtYsWGDBgSeJAYA@mail.gmail.com >
References: <SNT111-W18646B5C50D8B2D96AE68DE19C0@phx.gbl >
<506307E2.4060504@ulb.ac.be >
<1348668859243-5725505.post@n5.nabble.com >
<50630E6F.5040605@ulb.ac.be >
<SNT111-W245F4E05303F533C4356C2E19C0@phx.gbl >
<506316B1.70900@ulb.ac.be >
<SNT111-W28A5F2A90FEADAB0F881F9E19C0@phx.gbl >
<CA+MedixJ3jH2q3HBRqDj54SJRq-hi_ktBofUvSspYqdyzuf41w@mail.gmail.com >
<SNT111-W2025F9BE2B94A56FE1CE13E19C0@phx.gbl >
<CA+Medix+6HXJT3F67c=Wn2XPtHeo+2qzB6f1YSS_XMNDA6xf6A@mail.gmail.com >
<SNT111-W2098D2E9847FEE99A5828E19C0@phx.gbl >
<CA+MedixsJbhjPGsKiWsp3ENjAnR3r3Ajw_nVtYsWGDBgSeJAYA@mail.gmail.com >
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=0.00..353722.89 rows=124893 width=16) (actual time=261158.061..10304193.501 rows=99 loops=1) Join Filter: ((t2."X" >= (t1.x_min)::double precision) AND (t2."X" <= (t1.x_max)::double precision) AND (t2."Y" >= (t1.y_min)::double precision) AND (t2."Y" <= (t1.y_max)::double precision)) -> Seq Scan on gps_22 t1 (cost=0.00..3431.80 rows=177480 width=44) (actual time=0.036..1399.621 rows=177480 loops=1) -> Materialize (cost=0.00..20572.83 rows=57 width=20) (actual time=0.012..10.274 rows=2924 loops=177480) -> Seq Scan on adresses_22 t2 (cost=0.00..20572.55 rows=57 width=20) (actual time=1570.240..1726.376 rows=2924 loops=1) Filter: ((id_maille_200m)::text = '0'::text)Total runtime: 10304211.648 ms
Nested Loop (cost=0.00..88186069.17 rows=33397899 width=16) (actual time=3060.373..3060.373 rows=0 loops=1) Join Filter: ((t2."X" >= (t1.x_min)::double precision) AND (t2."X" <= (t1.x_max)::double precision) AND (t2."Y" >= (t1.y_min)::double precision) AND (t2."Y" <= (t1.y_max)::double precision)) -> Seq Scan on gps_31 t1 (cost=0.00..3096.38 rows=161738 width=44) (actual time=4.612..442.935 rows=161738 loops=1) -> Materialize (cost=0.00..12562.25 rows=16726 width=20) (actual time=0.012..0.012 rows=0 loops=161738) -> Seq Scan on adresses_31 t2 (cost=0.00..12478.62 rows=16726 width=20) (actual time=1504.082..1504.082 rows=0 loops=1) Filter: ((id_maille_200m)::text = '0'::text)Total runtime: 3060.469 ms
Nested Loop (cost=0.00..84287659.70 rows=31920943 width=64) (actual time=220198.891..32665395.631 rows=21409 loops=1) Join Filter: ((t2."X" >= (t1.x_min)::double precision) AND (t2."X" <= (t1.x_max)::double precision) AND (t2."Y" >= (t1.y_min)::double precision) AND (t2."Y" <= (t1.y_max)::double precision)) -> Seq Scan on gps_67 t1 (cost=0.00..2350.55 rows=121555 width=44) (actual time=0.038..1570.994 rows=121555 loops=1) -> Materialize (cost=0.00..14072.09 rows=21271 width=20) (actual time=0.001..34.394 rows=22540 loops=121555) -> Seq Scan on adresses_67 t2 (cost=0.00..13965.74 rows=21271 width=20) (actual time=0.032..1283.087 rows=22540 loops=1) Filter: ((id_maille_200m)::text = '0'::text)Total runtime: 32665478.631 ms
Rude - Last Territory
Ou écouter ?http://www.deezer.com/fr/music/last-territory/the-last-hope-3617781 (Post-apocalyptic Metal)http://www.deezer.com/fr/music/rude-undertaker (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=uo%3D4
> Date: Wed, 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
>
> On 26 September 2012 19:09, FFW_Rude <FFW_Rude@hotmail.com> wrote:
> > Could you explain what you are asking me to do because i don't really know
> > what i'm doing...
>
> postgresql-contrib packages contains pgbench tool on Ubuntu.
>
> For example postgresql-contrib-9.1_9.1.3-2_i386.deb on Ubuntu 12.04 contains:
> /usr/lib/postgresql/9.1/bin/pgbench
>
>
> > i don't have pgbench in it. It is still pgbench command not found...
>
> You need to run pgbench as postgres user.
> For example on CentOS:
> # su - postgres
> $ pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
>
>
> --
> With best regards,
> Nikolay
=
view thread (21+ messages) latest in thread
Message-ID: <SNT111-W32B747BB89423F7197F456E1830@phx.gbl>
Permalink: ../SNT111-W32B747BB89423F7197F456E1830@phx.gbl/
Also on: postgresql.org/message-id/SNT111-W32B747BB89423F7197F456E1830@phx.gbl
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ffw_rude@hotmail.com, lystor@gmail.com
Subject: Re: Same query doing slow then quick
In-Reply-To: <SNT111-W32B747BB89423F7197F456E1830@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox