Re: query very slow when enable_seqscan=on

Tomasz Ostrowski <tometzky@batory.org.pl> writes:
> I have a very slow query when enable_seqscan=on and very fast when
> enable_seqscan=off.

Here's your problem:

>          ->  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)

If it were estimating something like the actual number of rows matching
that filter, it'd never have chosen a nestloop plan like that.

How many rows are there in the organization table?

This is probably the fault of the pattern-selectivity heuristic: it's
far too optimistic about long match strings eliminating a lot of rows.
I think there's been some discussion of modifying that logic but no
one's really stepped up with a better idea.

            regards, tom lane