Dear Postgresql Team:
I have some strange behavior on simple queries (i don't know if is a
bug, but i prefer to make a feature request), buy we need to tell to the
planner a different index priority. For example, i have a table called
"calls" with 11 million records, and i need to take the min() and max()
values of the primary key with the criteria of another field (index too)...
explain ANALYZE
select id
from calls
where campaign_id =3D 15603
order by id limit 1;
QUERY PLAN
---------------------------------------------------------------------------=
-----------------
Limit (cost=3D0.00..211.17 rows=3D1 width=3D4) (actual
time=3D186253.403..186253.404 rows=3D1 loops=3D1)
-> Index Scan using calls_pkey on calls (cost=3D0.00..656740.94
rows=3D3110 width=3D4) (actual time=3D186253.398..186253.398 rows=3D1 loops=
=3D1)
Filter: (campaign_id =3D 15603)
Total runtime: 186253.449 ms
(4 rows)
you can see a big amount of time to make the request!!!, but i see the
planner is doing the right think, i need more priority on the criteria
filter index. Let me show you a little workaround:
explain ANALYZE
select id
from calls
where campaign_id =3D 15603 and
campaign_id =3D 15603=20
order by id limit 1;
QUERY PLAN
---------------------------------------------------------------------------=
-----------------
Limit (cost=3D3.05..3.05 rows=3D1 width=3D4) (actual time=3D2.954..2.956
rows=3D1 loops=3D1)
-> Sort (cost=3D3.05..3.05 rows=3D1 width=3D4) (actual time=3D2.948..2=
.948
rows=3D1 loops=3D1)
Sort Key: id
-> Index Scan using in_calls_campaign_id on calls=20
(cost=3D0.00..3.04 rows=3D1 width=3D4) (actual time=3D0.042..1.396 rows=3D7=
36 loops=3D1)
Index Cond: ((campaign_id =3D 15603) AND (campaign_id =3D 15=
603))
Total runtime: 3.062 ms
(6 rows)
Why i repeat the condition on the WHERE clause?... Is for instruct the
planner to bring more priority to a more complex statement.
ummm.... much better, and the request for the parser and the planner i
to use this kind of syntax to give priority to a query:
select id
from calls
where (campaign_id =3D 15603)
order by id limit 1;
if you note in other parser operations like a math calculation, the use
of parenthesis bring priority in the sql statement. Is posible to make a
parenthesis notation to instruct the planner to make another inference
in the execution priority?
I use PostgreSQL 8.1.3 and 8.2.3
Thanks in advanced.... Fernando Romo (pop@cofradia.org).