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
