From: | Ben Beecher <benbeecher(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Custom operator class costs |
Date: | 2011-03-16 14:10:02 |
Message-ID: | AANLkTikjpZojDysWA5au+2pYPJeag4toJjh62YncwMog@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hey!
I'm having some trouble optimizing a query that uses a custom operator class.
#Postgres has given me a solution for natural sort -
http://www.rhodiumtoad.org.uk/junk/naturalsort.sql
I'm trying to run it over a huge table - when running it on demand,
the data needs to be dumped to memory and sorted.
Sort (cost=31299.83..31668.83 rows=369 width=31)
Sort Key: name
-> Seq Scan on solutions_textbookpage (cost=0.00..25006.55
rows=369 width=31)
Filter: (active AND (textbook_id = 263))
That's obviously too slow. I've created an index using the custom
operator class, so I don't have to do the sort every time I try to
sort.
Index Scan Backward using natural_page_name_textbook on
solutions_textbookpage (cost=0.00..650.56 rows=371 width=31) (actual
time=0.061..0.962 rows=369 loops=1)
Index Cond: (textbook_id = 263)
Filter: active
Obviously a little faster!
The problem I'm having is that because operator classes have a low
cost estimation pg missestimates and tries to do the sort on demand
rather than using the index.
I can get pg to use the index by either jacking up cpu_operator_cost
or lowering random_page_cost. Is this the best way to do that, or is
there a smarter way to ensure that pg uses this index when I need it.
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Marshall | 2011-03-16 16:36:53 | Re: Help with Query Tuning |
Previous Message | Adarsh Sharma | 2011-03-16 09:13:38 | Help with Query Tuning |