pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSame query doing slow then quick
21+ messages / 5 participants
[nested] [flat]
* Same query doing slow then quick
@ 2012-09-26 12:27 FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 12:27 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm new here so i hope i don't do mistakes.
I'm having a serious performance issue in postgresql.
I have tables containing adresses with X,Y GPS coordinates and tables with
zoning and square of gps coordinates.
Basicly it looks like
adresses_01 (id,X,Y)
gps_01 (id,x_min,x_max,y_min,y_max).
[code]
"
SELECT
t2.id,
FROM
tables_gps.gps_01 t1
INNER JOIN
tables_adresses.adresses_01 t2
ON
t2."X" BETWEEN t1.x_min AND t1.x_max AND t2."Y" BETWEEN t1.y_min AND
t1.y_max
WHERE
t2.id='0'
"
[/code]
I have something like 250000rows in each table.
Now when i execute this on adresses_01 and gps_01, the request complete in a
few minutes.
But when doing it on adresses_02 and gps_02 (same number of rows
approximately) the query takes 5hours.
I have indexes on adresses on X,Y and an index in gps on
x_min,y_min,x_max,y_max.
Now i do updates in result of this query on ID (so i have an index on ID
too).
My question is ... Why ? (;o). And also, do i need to use CLUSTER (i don't
really understand what it does). And if so. Do i need to CLUSTER the id ? Or
the X,Y index ?
It may be not really clear so just ask questions if you don't get when i
mean or if you need specs or anything. I just moved from MySql to PostgreSql
last month.
Thanks in advance :)
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 13:03 FFW_Rude <FFW_Rude@hotmail.com>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 13:03 UTC (permalink / raw)
To: pgsql-performance
Here is the answer to Ray Stell who send me the wiki page of Slow Query. I
hope i detailed all you wanted (i basicly pasted the page and add my
answers).
Full Table and Index Schema:
schema tables_adresses
"Tables"
tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double
precision)).
"Indexes"
adresses_XX_pkey (Primary key, btree)
calcul_XX (non unique, Btree on X,Y)
schema tables_gps
"Tables"
tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max
(numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5)))
"Indexes"
calculs_XX (non unique Btree x_min,x_max,y_min,y_max)
gps_10_pkey (Primary key on id btree)
Approximate rows 250000.
No large objects in it (just data)
No NULL
receives a large number of UPDATEs or DELETEs regularly
is growing daily
I can't post an EXPLAIN ANALYZE because of the 6hour query time.
Postgres version: 9.1
History: was this query always slow, : "YES"
Hardware: Ubuntu server last version 32bits
Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables.
WAL Configuration: Whats a WAL ?
GUC Settings: i didn't change anything. All is standard.
shared_buffers should be 10% to 25% of available RAM (it's on 24MB and can't
go higher. The server has 4Gb)
effective_cache_size should be 75% of available RAM => I don't now what this
is.
Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a
difference? "No"
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 13:21 Julien Cigar <jcigar@ulb.ac.be>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Julien Cigar @ 2012-09-26 13:21 UTC (permalink / raw)
To: pgsql-performance
On 09/26/2012 15:03, FFW_Rude wrote:
> Here is the answer to Ray Stell who send me the wiki page of Slow Query. I
> hope i detailed all you wanted (i basicly pasted the page and add my
> answers).
>
> Full Table and Index Schema:
>
> schema tables_adresses
> "Tables"
> tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double
> precision)).
> "Indexes"
> adresses_XX_pkey (Primary key, btree)
> calcul_XX (non unique, Btree on X,Y)
>
> schema tables_gps
> "Tables"
> tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max
> (numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5)))
> "Indexes"
> calculs_XX (non unique Btree x_min,x_max,y_min,y_max)
> gps_10_pkey (Primary key on id btree)
>
> Approximate rows 250000.
> No large objects in it (just data)
> No NULL
> receives a large number of UPDATEs or DELETEs regularly
> is growing daily
>
> I can't post an EXPLAIN ANALYZE because of the 6hour query time.
>
> Postgres version: 9.1
>
> History: was this query always slow, : "YES"
>
> Hardware: Ubuntu server last version 32bits
>
> Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables.
>
> WAL Configuration: Whats a WAL ?
>
> GUC Settings: i didn't change anything. All is standard.
>
> shared_buffers should be 10% to 25% of available RAM (it's on 24MB and can't
> go higher. The server has 4Gb)
>
> effective_cache_size should be 75% of available RAM => I don't now what this
> is.
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I start with shared_buffers to 512MB and
effective_cache_size to 2GB
> Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a
> difference? "No"
default work_mem is very small, set it to something like 16MB
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
jcigar.vcf (text/x-vcard, 290B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
* Re: Same query doing slow then quick
@ 2012-09-26 13:36 FFW_Rude <FFW_Rude@hotmail.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 13:36 UTC (permalink / raw)
To: pgsql-performance
Hi,
Thank you for your answer.
It was already at 16MB and i upped it just this morning to 64MB. Still no change
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 06:22:35 -0700
From: ml-node+s1045698n5725493h43@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
On 09/26/2012 15:03, FFW_Rude wrote:
> Here is the answer to Ray Stell who send me the wiki page of Slow Query. I
> hope i detailed all you wanted (i basicly pasted the page and add my
> answers).
>
> Full Table and Index Schema:
>
> schema tables_adresses
> "Tables"
> tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double
> precision)).
> "Indexes"
> adresses_XX_pkey (Primary key, btree)
> calcul_XX (non unique, Btree on X,Y)
>
> schema tables_gps
> "Tables"
> tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max
> (numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5)))
> "Indexes"
> calculs_XX (non unique Btree x_min,x_max,y_min,y_max)
> gps_10_pkey (Primary key on id btree)
>
> Approximate rows 250000.
> No large objects in it (just data)
> No NULL
> receives a large number of UPDATEs or DELETEs regularly
> is growing daily
>
> I can't post an EXPLAIN ANALYZE because of the 6hour query time.
>
> Postgres version: 9.1
>
> History: was this query always slow, : "YES"
>
> Hardware: Ubuntu server last version 32bits
>
> Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables.
>
> WAL Configuration: Whats a WAL ?
>
> GUC Settings: i didn't change anything. All is standard.
>
> shared_buffers should be 10% to 25% of available RAM (it's on 24MB and can't
> go higher. The server has 4Gb)
>
> effective_cache_size should be 75% of available RAM => I don't now what this
> is.
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I start with shared_buffers to 512MB and
effective_cache_size to 2GB
> Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a
> difference? "No"
default work_mem is very small, set it to something like 16MB
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
jcigar.vcf (304 bytes) Download Attachment
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725493.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725495.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 13:49 Julien Cigar <jcigar@ulb.ac.be>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Julien Cigar @ 2012-09-26 13:49 UTC (permalink / raw)
To: pgsql-performance
On 09/26/2012 15:36, FFW_Rude wrote:
> Hi,
>
> Thank you for your answer.
>
> It was already at 16MB and i upped it just this morning to 64MB. Still
> no change
>
that's normal, please configure shared_buffers and effective_cache_size
properly
> 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 Fnac/
> http://recherche.fnac.com/fmia14622213/Last-Territory
> http://recherche.fnac.com/fmia14770622/Rude-Undertaker
>
> /iTunes/
> http://itunes.apple.com/us/artist/last-territory/id533857009?ign-mpt=uo%3D4
>
>
>
> ------------------------------------------------------------------------
> Date: Wed, 26 Sep 2012 06:22:35 -0700
> From: [hidden email] </user/SendEmail.jtp?type=node&node=5725495&i=0>
> To: [hidden email] </user/SendEmail.jtp?type=node&node=5725495&i=1>
> Subject: Re: Same query doing slow then quick
>
> On 09/26/2012 15:03, FFW_Rude wrote:
>
> > Here is the answer to Ray Stell who send me the wiki page of Slow
> Query. I
> > hope i detailed all you wanted (i basicly pasted the page and add my
> > answers).
> >
> > Full Table and Index Schema:
> >
> > schema tables_adresses
> > "Tables"
> > tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double
> > precision)).
> > "Indexes"
> > adresses_XX_pkey (Primary key, btree)
> > calcul_XX (non unique, Btree on X,Y)
> >
> > schema tables_gps
> > "Tables"
> > tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max
> > (numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5)))
> > "Indexes"
> > calculs_XX (non unique Btree x_min,x_max,y_min,y_max)
> > gps_10_pkey (Primary key on id btree)
> >
> > Approximate rows 250000.
> > No large objects in it (just data)
> > No NULL
> > receives a large number of UPDATEs or DELETEs regularly
> > is growing daily
> >
> > I can't post an EXPLAIN ANALYZE because of the 6hour query time.
> >
> > Postgres version: 9.1
> >
> > History: was this query always slow, : "YES"
> >
> > Hardware: Ubuntu server last version 32bits
> >
> > Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables.
> >
> > WAL Configuration: Whats a WAL ?
> >
> > GUC Settings: i didn't change anything. All is standard.
> >
> > shared_buffers should be 10% to 25% of available RAM (it's on 24MB
> and can't
> > go higher. The server has 4Gb)
> >
> > effective_cache_size should be 75% of available RAM => I don't now
> what this
> > is.
> before looking further, please configure shared_buffers and
> effective_cache_size properly, it's fundamental
> you'll probably need to raise SHMALL/SHMMAX, take a look at:
> http://www.postgresql.org/docs/current/static/kernel-resources.html
> for 4GB of RAM I start with shared_buffers to 512MB and
> effective_cache_size to 2GB
>
> > Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does
> it make a
> > difference? "No"
>
> default work_mem is very small, set it to something like 16MB
>
> >
> >
> >
> > --
> > View this message in context:
> http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html
> > Sent from the PostgreSQL - performance mailing list archive at
> Nabble.com.
> >
> >
>
>
> --
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
>
>
> --
> Sent via pgsql-performance mailing list ([hidden email]
> <http:///user/SendEmail.jtp?type=node&node=5725493&i=0;)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
> *jcigar.vcf* (304 bytes) Download Attachment
> <http://postgresql.1045698.n5.nabble.com/attachment/5725493/0/jcigar.vcf;
>
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725493.html
>
> To unsubscribe from Same query doing slow then quick, click here.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_h...;
>
>
> ------------------------------------------------------------------------
> View this message in context: RE: Same query doing slow then quick
> <http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725495.html;
> Sent from the PostgreSQL - performance mailing list archive
> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-performance-f2050081.html;
> at Nabble.com.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
jcigar.vcf (text/x-vcard, 304B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
* Re: Same query doing slow then quick
@ 2012-09-26 14:14 FFW_Rude <FFW_Rude@hotmail.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 14:14 UTC (permalink / raw)
To: pgsql-performance
Thank for you answer.
shared_buffer is at 24Mb
effective_cache_size at 2048Mb
What do you mean properly ? That's not really helping a novice...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 14:17 Julien Cigar <jcigar@ulb.ac.be>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Julien Cigar @ 2012-09-26 14:17 UTC (permalink / raw)
To: pgsql-performance
On 09/26/2012 16:14, FFW_Rude wrote:
> Thank for you answer.
>
> shared_buffer is at 24Mb
> effective_cache_size at 2048Mb
>
> What do you mean properly ? That's not really helping a novice...
>
from my previous mail:
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I would start with shared_buffers to 512MB and
effective_cache_size to 2GB
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
jcigar.vcf (text/x-vcard, 290B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
* Re: Same query doing slow then quick
@ 2012-09-26 14:19 FFW_Rude <FFW_Rude@hotmail.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
1 sibling, 0 replies; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 14:19 UTC (permalink / raw)
To: pgsql-performance
My bad. Did not see that part.
I tried to elevate buffer and SHMAX was a problem. I'll give it another try and will keep you posted.
Thank you.
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 07:17:56 -0700
From: ml-node+s1045698n5725506h46@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
On 09/26/2012 16:14, FFW_Rude wrote:
> Thank for you answer.
>
> shared_buffer is at 24Mb
> effective_cache_size at 2048Mb
>
> What do you mean properly ? That's not really helping a novice...
>
from my previous mail:
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I would start with shared_buffers to 512MB and
effective_cache_size to 2GB
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
jcigar.vcf (304 bytes) Download Attachment
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725506.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725508.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 14:41 FFW_Rude <FFW_Rude@hotmail.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
1 sibling, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 14:41 UTC (permalink / raw)
To: pgsql-performance
Ok done to 512Mb and 2048Mb
I'm relaunching. See you in a few hours (so tommorrow)
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 07:17:56 -0700
From: ml-node+s1045698n5725506h46@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
On 09/26/2012 16:14, FFW_Rude wrote:
> Thank for you answer.
>
> shared_buffer is at 24Mb
> effective_cache_size at 2048Mb
>
> What do you mean properly ? That's not really helping a novice...
>
from my previous mail:
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I would start with shared_buffers to 512MB and
effective_cache_size to 2GB
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
jcigar.vcf (304 bytes) Download Attachment
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725506.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725518.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 14:52 Julien Cigar <jcigar@ulb.ac.be>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Julien Cigar @ 2012-09-26 14:52 UTC (permalink / raw)
To: pgsql-performance
On 09/26/2012 16:41, FFW_Rude wrote:
> Ok done to 512Mb and 2048Mb
>
> I'm relaunching. See you in a few hours (so tommorrow)
with 250 000 rows and proper indexes it should run in less than a second.
be sure your indexes are set properly and that they're used (use EXPLAIN
ANALYZE for that) within your query ...
>
> 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 Fnac/
> http://recherche.fnac.com/fmia14622213/Last-Territory
> http://recherche.fnac.com/fmia14770622/Rude-Undertaker
>
> /iTunes/
> http://itunes.apple.com/us/artist/last-territory/id533857009?ign-mpt=uo%3D4
>
>
>
> ------------------------------------------------------------------------
> Date: Wed, 26 Sep 2012 07:17:56 -0700
> From: [hidden email] </user/SendEmail.jtp?type=node&node=5725518&i=0>
> To: [hidden email] </user/SendEmail.jtp?type=node&node=5725518&i=1>
> Subject: Re: Same query doing slow then quick
>
> On 09/26/2012 16:14, FFW_Rude wrote:
> > Thank for you answer.
> >
> > shared_buffer is at 24Mb
> > effective_cache_size at 2048Mb
> >
> > What do you mean properly ? That's not really helping a novice...
> >
>
> from my previous mail:
>
> before looking further, please configure shared_buffers and
> effective_cache_size properly, it's fundamental
> you'll probably need to raise SHMALL/SHMMAX, take a look at:
> http://www.postgresql.org/docs/current/static/kernel-resources.html
> for 4GB of RAM I would start with shared_buffers to 512MB and
> effective_cache_size to 2GB
>
> >
> >
> > --
> > View this message in context:
> http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
> > Sent from the PostgreSQL - performance mailing list archive at
> Nabble.com.
> >
> >
>
>
> --
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
>
>
> --
> Sent via pgsql-performance mailing list ([hidden email]
> <http:///user/SendEmail.jtp?type=node&node=5725506&i=0;)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
> *jcigar.vcf* (304 bytes) Download Attachment
> <http://postgresql.1045698.n5.nabble.com/attachment/5725506/0/jcigar.vcf;
>
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725506.html
>
> To unsubscribe from Same query doing slow then quick, click here.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_h...;
>
>
> ------------------------------------------------------------------------
> View this message in context: RE: Same query doing slow then quick
> <http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725518.html;
> Sent from the PostgreSQL - performance mailing list archive
> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-performance-f2050081.html;
> at Nabble.com.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
jcigar.vcf (text/x-vcard, 304B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
* Re: Same query doing slow then quick
@ 2012-09-26 15:18 FFW_Rude <FFW_Rude@hotmail.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 15:18 UTC (permalink / raw)
To: pgsql-performance
It sure does not take less than a second :(
37minutes in and no results. I'm gonna wait until the end to see the result of the explain
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 08:07:08 -0700
From: ml-node+s1045698n5725526h66@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
On 09/26/2012 16:41, FFW_Rude wrote:
Ok done to 512Mb and 2048Mb
I'm relaunching. See you in a few hours (so tommorrow)
with 250 000 rows and proper indexes it should run in less than a
second.
be sure your indexes are set properly and that they're used (use
EXPLAIN ANALYZE for that) within your query ...
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 Fnac
http://recherche.fnac.com/fmia14622213/Last-Territory
http://recherche.fnac.com/fmia14770622/Rude-Undertaker
iTunes
http://itunes.apple.com/us/artist/last-territory/id533857009?ign-mpt=uo%3D4
Date: Wed, 26 Sep 2012 07:17:56 -0700
From: [hidden
email]
To: [hidden
email]
Subject: Re: Same query doing slow then quick
On 09/26/2012 16:14, FFW_Rude wrote:
> Thank for you answer.
>
> shared_buffer is at 24Mb
> effective_cache_size at 2048Mb
>
> What do you mean properly ? That's not really helping a
novice...
>
from my previous mail:
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I would start with shared_buffers to 512MB
and
effective_cache_size to 2GB
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725505.html
> Sent from the PostgreSQL - performance mailing list
archive at Nabble.com.
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list ([hidden
email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
jcigar.vcf
(304 bytes) Download
Attachment
If you reply to this email,
your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725506.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
View this message in context: RE:
Same query doing slow then quick
Sent from the PostgreSQL
- performance mailing list archive at Nabble.com.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
jcigar.vcf (422 bytes) Download Attachment
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725526.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725527.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 15:33 Nikolay Ulyanitsky <lystor@gmail.com>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Nikolay Ulyanitsky @ 2012-09-26 15:33 UTC (permalink / raw)
To: FFW_Rude <FFW_Rude@hotmail.com>; +Cc: pgsql-performance
Hi, FFW_Rude
1. Benchmark the device with your PostgreSQL DB:
# hdparm -tT /dev/sda
/dev/sda:
Timing cached reads: 6604 MB in 2.00 seconds = 3303.03 MB/sec
Timing buffered disk reads: 1316 MB in 3.00 seconds = 438.18 MB/sec
2. Benchmark your PostgreSQL with pgbench:
Set "fsync = off" on /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql restart
# su - postgres
$ psql
# create database pgbench;
# \q
# pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
tps = 5670.635648 (including connections establishing)
tps = 5673.630345 (excluding connections establishing)[/code]
Set "fsync = on" on /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql restart
--
With best regards,
Nikolay
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 15:38 FFW_Rude <FFW_Rude@hotmail.com>
parent: Nikolay Ulyanitsky <lystor@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 15:38 UTC (permalink / raw)
To: pgsql-performance
Hi,
root@testBI:/etc/postgresql/9.1/main# hdparm -tT /dev/sda
/dev/sda: Timing cached reads: 892 MB in 2.01 seconds = 444.42 MB/sec Timing buffered disk reads: 190 MB in 3.02 seconds = 62.90 MB/sec
Is fsync off by default ? I have#fsync = on (so it's off right ?).
pgbench is not found on my server. Do i have to apt-get install pgbench ?
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 08:34:06 -0700
From: ml-node+s1045698n5725533h83@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
Hi, FFW_Rude
1. Benchmark the device with your PostgreSQL DB:
# hdparm -tT /dev/sda
/dev/sda: Timing cached reads: 6604 MB in 2.00 seconds = 3303.03 MB/sec
Timing buffered disk reads: 1316 MB in 3.00 seconds = 438.18 MB/sec
2. Benchmark your PostgreSQL with pgbench:
Set "fsync = off" on /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql restart
# su - postgres$ psql# create database pgbench;# \q# pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
tps = 5670.635648 (including connections establishing)tps = 5673.630345 (excluding connections establishing)[/code]
Set "fsync = on" on /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql restart
--With best regards,Nikolay
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725533.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725536.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 15:52 Nikolay Ulyanitsky <lystor@gmail.com>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Nikolay Ulyanitsky @ 2012-09-26 15:52 UTC (permalink / raw)
To: FFW_Rude <FFW_Rude@hotmail.com>; +Cc: pgsql-performance
On 26 September 2012 18:38, FFW_Rude <FFW_Rude@hotmail.com> wrote:
> root@testBI:/etc/postgresql/9.1/main# hdparm -tT /dev/sda
> Timing cached reads: 892 MB in 2.01 seconds = 444.42 MB/sec
> Timing buffered disk reads: 190 MB in 3.02 seconds = 62.90 MB/sec
It's OK for single HDD.
> Is fsync off by default ? I have
> #fsync = on (so it's off right ?).
Disable fsync for pgbench temporarily.
> pgbench is not found on my server. Do i have to apt-get install pgbench ?
Install the postgresql-contrib deb:
http://pkgs.org/download/postgresql-contrib
# sudo apt-get update
# sudo apt-get install postgresql-contrib
--
With best regards,
Nikolay
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 16:05 Undertaker Rude <ffw_rude@hotmail.com>
parent: Nikolay Ulyanitsky <lystor@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Undertaker Rude @ 2012-09-26 16:05 UTC (permalink / raw)
To: lystor@gmail.com; +Cc: pgsql-performance
Ok i'm installing. Can't stop the server right now. I'm gonna have to get back to you tomorrow afternoon (have other tasks that need to run from now until tomorrow by 1pm)
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 18:52:27 +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 18:38, FFW_Rude <FFW_Rude@hotmail.com> wrote:
> > root@testBI:/etc/postgresql/9.1/main# hdparm -tT /dev/sda
> > Timing cached reads: 892 MB in 2.01 seconds = 444.42 MB/sec
> > Timing buffered disk reads: 190 MB in 3.02 seconds = 62.90 MB/sec
>
> It's OK for single HDD.
>
>
> > Is fsync off by default ? I have
> > #fsync = on (so it's off right ?).
>
> Disable fsync for pgbench temporarily.
>
>
> > pgbench is not found on my server. Do i have to apt-get install pgbench ?
>
> Install the postgresql-contrib deb:
> http://pkgs.org/download/postgresql-contrib
>
> # sudo apt-get update
> # sudo apt-get install postgresql-contrib
>
>
> --
> With best regards,
> Nikolay
=
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 16:09 FFW_Rude <FFW_Rude@hotmail.com>
parent: Nikolay Ulyanitsky <lystor@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: FFW_Rude @ 2012-09-26 16:09 UTC (permalink / raw)
To: pgsql-performance
so installing postgresql-contrib stopped my server and i don't have pgbench in it. It is still pgbench command not found...
Could you explain what you are asking me to do because i don't really know what i'm doing...
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 08:53:29 -0700
From: ml-node+s1045698n5725542h62@n5.nabble.com
To: FFW_Rude@hotmail.com
Subject: Re: Same query doing slow then quick
On 26 September 2012 18:38, FFW_Rude <[hidden email]> wrote:
> root@testBI:/etc/postgresql/9.1/main# hdparm -tT /dev/sda
> Timing cached reads: 892 MB in 2.01 seconds = 444.42 MB/sec
> Timing buffered disk reads: 190 MB in 3.02 seconds = 62.90 MB/sec
It's OK for single HDD.
> Is fsync off by default ? I have
> #fsync = on (so it's off right ?).
Disable fsync for pgbench temporarily.
> pgbench is not found on my server. Do i have to apt-get install pgbench ?
Install the postgresql-contrib deb:
http://pkgs.org/download/postgresql-contrib
# sudo apt-get update
# sudo apt-get install postgresql-contrib
--
With best regards,
Nikolay
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725542.html
To unsubscribe from Same query doing slow then quick, click here.
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725549.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-26 16:30 Nikolay Ulyanitsky <lystor@gmail.com>
parent: FFW_Rude <FFW_Rude@hotmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Nikolay Ulyanitsky @ 2012-09-26 16:30 UTC (permalink / raw)
To: FFW_Rude <FFW_Rude@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-27 08:33 Undertaker Rude <ffw_rude@hotmail.com>
parent: Nikolay Ulyanitsky <lystor@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Undertaker Rude @ 2012-09-27 08:33 UTC (permalink / raw)
To: lystor@gmail.com; +Cc: pgsql-performance
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
=
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-09-27 09:01 Undertaker Rude <ffw_rude@hotmail.com>
parent: Nikolay Ulyanitsky <lystor@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Undertaker Rude @ 2012-09-27 09:01 UTC (permalink / raw)
To: lystor@gmail.com; +Cc: pgsql-performance
So i tried to run your pgbench command with the postgres user but it's stil telling me command not found
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
=
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-10-07 14:27 Ants Aasma <ants@cybertec.at>
parent: Undertaker Rude <ffw_rude@hotmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Ants Aasma @ 2012-10-07 14:27 UTC (permalink / raw)
To: Undertaker Rude <ffw_rude@hotmail.com>; +Cc: lystor@gmail.com; pgsql-performance
Sorry for the late answer, I was going through my e-mail backlog and
noticed that this question hadn't been answered.
On Thu, Sep 27, 2012 at 11:33 AM, Undertaker Rude <ffw_rude@hotmail.com> wrote:
> 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
As you can see from the explain plan, postgresql is not using any
indexes here. The reason is the type mismatch between the X and x_min
columns. Use matching types between tables to enable index use. The
same goes for the id column, if the column type is integer use a
numeric literal 0 not a text literal '0'.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Same query doing slow then quick
@ 2012-10-08 07:39 Undertaker Rude <ffw_rude@hotmail.com>
parent: Ants Aasma <ants@cybertec.at>
0 siblings, 0 replies; 21+ messages in thread
From: Undertaker Rude @ 2012-10-08 07:39 UTC (permalink / raw)
To: ants@cybertec.at; +Cc: lystor@gmail.com; pgsql-performance
Oh, thankx. I forgot to put the answer i got from another site. I was told to use box and point type and create an index on it and it works really well !
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: Sun, 7 Oct 2012 17:27:02 +0300
> Subject: Re: [PERFORM] Same query doing slow then quick
> From: ants@cybertec.at
> To: ffw_rude@hotmail.com
> CC: lystor@gmail.com; pgsql-performance@postgresql.org
>
> Sorry for the late answer, I was going through my e-mail backlog and
> noticed that this question hadn't been answered.
>
> On Thu, Sep 27, 2012 at 11:33 AM, Undertaker Rude <ffw_rude@hotmail.com> wrote:
> > 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
>
> As you can see from the explain plan, postgresql is not using any
> indexes here. The reason is the type mismatch between the X and x_min
> columns. Use matching types between tables to enable index use. The
> same goes for the id column, if the column type is integer use a
> numeric literal 0 not a text literal '0'.
>
> Regards,
> Ants Aasma
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt
> Web: http://www.postgresql-support.de
=
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2012-10-08 07:39 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-26 12:27 Same query doing slow then quick FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 13:03 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 13:21 ` Julien Cigar <jcigar@ulb.ac.be>
2012-09-26 13:36 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 13:49 ` Julien Cigar <jcigar@ulb.ac.be>
2012-09-26 14:14 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 14:17 ` Julien Cigar <jcigar@ulb.ac.be>
2012-09-26 14:19 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 14:41 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 14:52 ` Julien Cigar <jcigar@ulb.ac.be>
2012-09-26 15:18 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 15:33 ` Nikolay Ulyanitsky <lystor@gmail.com>
2012-09-26 15:38 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 15:52 ` Nikolay Ulyanitsky <lystor@gmail.com>
2012-09-26 16:05 ` Undertaker Rude <ffw_rude@hotmail.com>
2012-09-26 16:09 ` FFW_Rude <FFW_Rude@hotmail.com>
2012-09-26 16:30 ` Nikolay Ulyanitsky <lystor@gmail.com>
2012-09-27 08:33 ` Undertaker Rude <ffw_rude@hotmail.com>
2012-10-07 14:27 ` Ants Aasma <ants@cybertec.at>
2012-10-08 07:39 ` Undertaker Rude <ffw_rude@hotmail.com>
2012-09-27 09:01 ` Undertaker Rude <ffw_rude@hotmail.com>
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