Planner create a slow plan without an available index - Mailing list pgsql-general

Hi All,

I got a weird problem with the planner which cause my queries to take
ages... ill try to explain it shortly and summarized... :)

I got the following table (which got 1.2 million rows):

                      Table "public.items"
            Column           |     Type     |      Modifiers
----------------------------+--------------+---------------------
  items_id                   | text         | not null
  price                    | numeric(8,2) | not null
  left                       | integer      |
  right                      | integer      |
Indexes:
     "items_items_id_key" UNIQUE, btree (items_id)
     "items_left" btree (left)
     "items_left_right" btree (left, right)

 From that table I created the next table in order to save "ORDER BY
price" at the queries:

bh.com=# CREATE TABLE items_price AS SELECT * FROM items ORDER BY price;

After the creation of the table I created indexes which are exactly the
same as the items table has (the source table).
Later I ran on both tables "VACUUM FULL ANALYZE".


Now here start the weird stuff....

bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left>=(SELECT left
FROM category WHERE category_id=821) AND right<=(SELECT right FROM
category WHERE category_id=821) OFFSET 24 LIMIT 13;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=58.27..86.55 rows=13 width=619) (actual
time=0.811..130.993 rows=9 loops=1)
    InitPlan
      ->  Index Scan using category_pkey on category  (cost=0.00..3.03
rows=1 width=4) (actual time=0.118..0.124 rows=1 loops=1)
            Index Cond: (category_id = 821)
      ->  Index Scan using category_pkey on category  (cost=0.00..3.03
rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1)
            Index Cond: (category_id = 821)
    ->  Index Scan using items_left_right on items
(cost=0.00..294897.72 rows=135553 width=619) (actual time=0.314..130.815
rows=33 loops=1)
          Index Cond: ((left >= $0) AND (right <= $1))
  Total runtime: 131.140 ms
(9 rows)

bh.com=# ANALYZE items;
ANALYZE
bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left>=(SELECT left
FROM category WHERE category_id=821) AND right<=(SELECT right FROM
category WHERE category_id=821) OFFSET 24 LIMIT 13;


QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=57.11..84.77 rows=13 width=626) (actual
time=45.512..145316.423 rows=9 loops=1)
    InitPlan
      ->  Index Scan using category_pkey on category  (cost=0.00..3.03
rows=1 width=4) (actual time=0.185..0.191 rows=1 loops=1)
            Index Cond: (category_id = 821)
      ->  Index Scan using category_pkey on category  (cost=0.00..3.03
rows=1 width=4) (actual time=0.026..0.032 rows=1 loops=1)
            Index Cond: (category_id = 821)
    ->  Index Scan using items_left on items  (cost=0.00..293408.52
rows=137924 width=626) (actual time=45.008..145316.246 rows=33 loops=1)
          Index Cond: (left >= $0)
          Filter: (right <= $1)
  Total runtime: 145316.590 ms
(10 rows)


The "ANALYZE items" actually made the planner work without the INDEX and
by that the query became a lot slower! after running VACUUM ANALYZE on
the items table I receive good results back again.
Now I do know the diffrence between the 2 actions (VACUUM ANALYZE vs.
ANALYZE) but whats bug me is that when I do the exact same operations on
items_price (which is the same table exactly with the same indexes just
ordered diffrently) I receive a slow result no matter what I do!

I tried to mess with "ALTER TABLE items_price ALTER right SET STATISTICS
;" (and also on left) with diffrent values up to even 1000 but that
didnt help a bit (I did ran VACUUM ANALYZE after each change).

I'm quite clueless and also quite in a hurry to finish this project so
any help or a piece of clue will be welcomed gladly!

Thanks alot in advance (even only for reading what I wrote :P),
Ben-Nes Yonatan
Canaan Surfing ltd.
http://www.canaan.net.il