priority index on planner (feature request)

From: Fernando Romo <pop(at)cofradia(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: priority index on planner (feature request)
Date: 2007-05-23 17:57:12
Message-ID: 46548078.7030708@cofradia.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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 and 8.2.3

Thanks in advanced.... Fernando Romo (pop(at)cofradia(dot)org).

Browse pgsql-bugs by date

  From Date Subject
Next Message Fernando Romo 2007-05-23 23:28:17 BUG #3300: priority index on planner (feature request)
Previous Message Tom Lane 2007-05-23 15:59:43 Re: ERROR: invalid input syntax for integer: ""