From: | Bill McGonigle <bill(at)bfccomputing(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Too Many OR's? |
Date: | 2005-11-15 19:12:23 |
Message-ID: | e11403f0198a3591a66d8c28f0cd9b61@bfccomputing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a query that's making the planner do the wrong thing (for my
definition of wrong) and I'm looking for advice on what to tune to make
it do what I want.
The query consists or SELECT'ing a few fields from a table for a large
number of rows. The table has about seventy thousand rows and the user
is selecting some subset of them. I first do a SELECT...WHERE to
determine the unique identifiers I want (works fine) and then I do a
SELECT WHERE IN giving the list of id's I need additional data on
(which I see from EXPLAIN just gets translated into a very long list of
OR's).
Everything works perfectly until I get to 65301 rows. At 65300 rows,
it does an index scan and takes 2197.193 ms. At 65301 rows it switches
to a sequential scan and takes 778951.556 ms. Values known not to
affect this are: work_mem, effective_cache_size. Setting
random_page_cost from 4 to 1 helps (79543.214 ms) but I'm not really
sure what '1' means, except it's relative. Of course, setting
'enable_seqscan false' helps immensely (2337.289 ms) but that's as
inelegant of a solution as I've found - if there were other databases
on this install that wouldn't be the right approach.
Now I can break this down into multiple SELECT's in code, capping each
query at 65300 rows, and that's a usable workaround, but academically
I'd like to know how to convince the planner to do it my way. It's
making a bad guess about something but I'm not sure what. I didn't see
any hard-coded limits grepping through the source (though it is close
to the 16-bit unsigned boundry - probably coincidental) so if anyone
has ideas or pointers to how I might figure out what's going wrong that
would be helpful.
Thanks,
-Bill
-----
Bill McGonigle, Owner Work: 603.448.4440
BFC Computing, LLC Home: 603.448.1668
bill(at)bfccomputing(dot)com Mobile: 603.252.2606
http://www.bfccomputing.com/ Pager: 603.442.1833
Jabber: flowerpt(at)gmail(dot)com Text: bill+text(at)bfccomputing(dot)com
Blog: http://blog.bfccomputing.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Joost Kraaijeveld | 2005-11-15 19:31:56 | Re: Performance PG 8.0 on dual opteron / 4GB / 3ware |
Previous Message | James Mello | 2005-11-15 19:07:40 | Re: Hardware/OS recommendations for large databases ( 5TB) |