Mime-Version: 1.0 (Apple Message framework v623)
Content-Transfer-Encoding: 7bit
Message-Id: <e11403f0198a3591a66d8c28f0cd9b61@bfccomputing.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed
To: pgsql-performance@postgresql.org
From: Bill McGonigle <bill@bfccomputing.com>
Subject: Too Many OR's?
Date: Tue, 15 Nov 2005 14:12:23 -0500

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@bfccomputing.com           Mobile: 603.252.2606
http://www.bfccomputing.com/    Pager: 603.442.1833
Jabber: flowerpt@gmail.com      Text: bill+text@bfccomputing.com
Blog: http://blog.bfccomputing.com/