Thread: different clients, different query plans

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

Uwe Bartels <uwe.bartels@gmail.com> wrote:

> I have a java application which generates inperformant query
> plans.

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

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

It is the cast of the table columns to double precision which is
taking the index out of play.

What are the data types of those columns?  What does the code look
like where you're setting the values for the parameters?  If nothing
else, writing the query so that the parameters are cast to the right
type before use might solve the problem, but I would start by
looking at the object classes used in the Java app.

-Kevin

the types are integer.
excellent!
you saved my weekend.

Uwe

Uwe Bartels
Systemarchitect - Freelancer
mailto: uwe.bartels@gmail.com
tel: +49 172 3899006
profile: https://www.xing.com/profile/Uwe_Bartels
website: http://www.uwebartels.com