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
[EMAIL PROTECTED]           Mobile: 603.252.2606
http://www.bfccomputing.com/    Pager: 603.442.1833
Jabber: [EMAIL PROTECTED]      Text: [EMAIL PROTECTED]
Blog: http://blog.bfccomputing.com/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to