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

different clients, different query plans

From: Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: different clients, different query plans
Date: 2011-02-18 14:29:01
Message-ID: AANLkTimOcusrOyjvT7JxmCbtCzJbTVrxwp79-otkEeUZ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I have a java application which generates inperformant query plans.
I checked the query plan from the java application via auto_explain module
and I compared the plan which I generate in psql.
They are different and I have no idea how I can convince the java
application to use the index.

the query plan i generate via psql is:

test=# prepare s as  SELECT COUNT(1) AS AMOUNT

test-# FROM NNDB.POI_LOCATION P

test-# WHERE P.LON BETWEEN $1 AND $2

test-# AND P.LAT BETWEEN $3 AND $4 limit $5;

PREPARE

test=# explain execute s(994341, 994377, 5355822, 5355851, 1);

                                           QUERY PLAN

-------------------------------------------------------------------------------------------------

Limit  (cost=17.09..17.10 rows=1 width=0)

   ->  Aggregate  (cost=17.09..17.10 rows=1 width=0)

         ->  Bitmap Heap Scan on poi_location p  (cost=9.42..17.08 rows=2
width=0)

               Recheck Cond: ((lat >= $3) AND (lat <= $4) AND (lon >= $1)
AND (lon <= $2))

               ->  Bitmap Index Scan on nx_poilocation_lat_lon
(cost=0.00..9.42 rows=2 width=0)

                     Index Cond: ((lat >= $3) AND (lat <= $4) AND (lon >=
$1) AND (lon <= $2))

(6 rows)

the query plan from the java application is:

2011-02-18 15:10:02 CET LOG:  duration: 25.180 ms  plan:

        Limit  (cost=2571.79..2571.80 rows=1 width=0) (actual
time=25.172..25.172 rows=1 loops=1)

          Output: (count(1))

          ->  Aggregate  (cost=2571.79..2571.80 rows=1 width=0) (actual
time=25.171..25.171 rows=1 loops=1)

                Output: count(1)

                ->  Seq Scan on poi_location p  (cost=0.00..2571.78 rows=2
width=0) (actual time=25.168..25.168 rows=0 loops=1)

                      Output: location_id, road_link_id, link_id, side,
percent_from_ref, lat, lon, location_type

                      Filter: (((lon)::double precision >= $1) AND
((lon)::double precision <= $2) AND ((lat)::double precision >= $3) AND
((lat)::double precision <= $4))

I checked that neither the java application or the psql client uses any evil
non-default settings like enable_*
set enable_idxscan=off

Any hints may help.

best...
Uwe

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2011-02-18 14:58:29
Subject: Re: different clients, different query plans
Previous:From: bricklenDate: 2011-02-17 23:26:05
Subject: Re: application of KNN code to US zipcode searches?

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