Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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).




pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group