The following bug has been logged online:
Bug reference: 3300
Logged by: Fernando Romo
Email address: pop@cofradia.org
PostgreSQL version: 8.2.4
Operating system: CentOS 4.4/5
Description: priority index on planner (feature request)
Details:
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 = 15603
order by id limit 1;
QUERY PLAN
----------------------------------------------------------------------------
----------------
Limit (cost=0.00..211.17 rows=1 width=4) (actual
time=186253.403..186253.404 rows=1 loops=1)
-> Index Scan using calls_pkey on calls (cost=0.00..656740.94
rows=3110 width=4) (actual time=186253.398..186253.398 rows=1 loops=1)
Filter: (campaign_id = 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 = 15603 and
campaign_id = 15603
order by id limit 1;
QUERY PLAN
----------------------------------------------------------------------------
----------------
Limit (cost=3.05..3.05 rows=1 width=4) (actual time=2.954..2.956
rows=1 loops=1)
-> Sort (cost=3.05..3.05 rows=1 width=4) (actual time=2.948..2.948
rows=1 loops=1)
Sort Key: id
-> Index Scan using in_calls_campaign_id on calls
(cost=0.00..3.04 rows=1 width=4) (actual time=0.042..1.396 rows=736
loops=1)
Index Cond: ((campaign_id = 15603) AND (campaign_id =
15603))
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 = 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, 8.2.3 and 8.2.4
Thanks in advanced.... Fernando Romo (pop@cofradia.org).
