Skip site navigation (1) Skip section navigation (2)

Re: Same query doing slow then quick

From: Undertaker Rude <ffw_rude(at)hotmail(dot)com>
To: <ants(at)cybertec(dot)at>
Cc: <lystor(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Same query doing slow then quick
Date: 2012-10-08 07:39:26
Message-ID: SNT111-W4B0E4DDE4E182748D6813E1880@phx.gbl (view raw or flat)
Thread:
Lists: 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(at)cybertec(dot)at
> To: ffw_rude(at)hotmail(dot)com
> CC: lystor(at)gmail(dot)com; pgsql-performance(at)postgresql(dot)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(at)hotmail(dot)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
 		 	   		  

In response to

pgsql-performance by date

Next:From: Andrzej ZawadzkiDate: 2012-10-08 08:18:06
Subject: Strange behavior after upgrade from 9.0 to 9.2
Previous:From: KoriskDate: 2012-10-07 17:33:19
Subject: hash aggregation speedup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group