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

Planner create a slow plan without an available index

From: Ben-Nes Yonatan <da(at)canaan(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org, 22 <da(at)canaan(dot)co(dot)il>
Subject: Planner create a slow plan without an available index
Date: 2005-08-29 11:13:06
Message-ID: 4312EDC2.8080702@canaan.co.il (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-general by date

Next:From: Stijn HoopDate: 2005-08-29 13:55:20
Subject: revoke on database not working as expected
Previous:From: Surabhi Ahuja Date: 2005-08-29 07:43:00
Subject: Re: regarding threads and transactions - problem 2

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