From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomasz Ostrowski <tometzky@batory.org.pl>
Cc: pgsql-bugs@postgresql.org
Subject: Re: query very slow when enable_seqscan=on
Date: Mon, 03 Jul 2006 19:05:46 -0400
Message-ID: <7353.1151967946@sss.pgh.pa.us> (raw)
In-Reply-To: <20060703203105.GA2801@batory.org.pl>
References: <20060703203105.GA2801@batory.org.pl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tometzky@batory.org.pl, pgsql-bugs@postgresql.org
Subject: Re: query very slow when enable_seqscan=on
In-Reply-To: <7353.1151967946@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox