different clients, different query plans

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux