On Mon, 2006-07-03 at 22:31 +0200, Tomasz Ostrowski wrote: > I have a very slow query when enable_seqscan=on and very fast when > enable_seqscan=off. My schema looks like this (relevant columns > only): > PS. Actual table and column names are different (they're in Polish) > but I've translated them for better readability for english-speaking. Thanks > PS. I wonder if it makes sense to "enable_seqscan=off" for every client > if a database is small enough to fit in OS cache. You can set this for individual statements if you choose to. > -> Seq Scan on organization (cost=0.00..480.95 rows=1 > width=4) (actual time=0.071..69.702 rows=1892 loops=1) > Filter: ((organization_location)::text ~* > 'warszawa'::text) The issue is caused by the under-estimation of the number of rows in the table as a result of the regular expression comparison. As a result the planner thinks it can choose a nested loops scan, though ends up doing 1892 seq scans of persons, when it thought it would do only one. The under estimation is a known issue. Posting to -perform for the record. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
