Search Postgresql Archives

Planner create a slow plan without an available index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux