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