From: | Ants Aasma <ants(at)cybertec(dot)at> |
---|---|
To: | Undertaker Rude <ffw_rude(at)hotmail(dot)com> |
Cc: | lystor(at)gmail(dot)com, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Same query doing slow then quick |
Date: | 2012-10-07 14:27:02 |
Message-ID: | CA+CSw_vTJg28zpm5pqtMM=MNzL9_t1kZt6t-tHp+oQshRUc2OA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Ants Aasma | 2012-10-07 14:43:45 | Re: Guide to Posting Slow Query Questions |
Previous Message | virag | 2012-10-07 13:49:23 | UPDATE execution time is increasing |