From: Tomasz Ostrowski <tometzky@batory.org.pl>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@postgresql.org
Subject: Re: query very slow when enable_seqscan=on
Date: Tue, 4 Jul 2006 10:37:33 +0200
Message-ID: <20060704083733.GA20102@batory.org.pl> (raw)
In-Reply-To: <7353.1151967946@sss.pgh.pa.us>
References: <20060703203105.GA2801@batory.org.pl>
<7353.1151967946@sss.pgh.pa.us>
On Mon, 03 Jul 2006, Tom Lane wrote:
> > -> 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)
>
> How many rows are there in the organization table?
About 9000. And about 6500 persons. "Warszawa" is a biggest city in
Poland and a capital - many organizations are located there.
> 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.
I think because there is no good solution to this - no statistical
information is going to predict how much data will match a regular
expression. Maybe in this situation an algorithm should be
pessimistic - that it will return all rows, or all non-null rows or
all rows no shorter than matching string (if it's a string and not
for example regex like [abcdefghijklmnopqrstuvwxyz] which is long but
will match basicaly everything). In my opinion it is better to
overestimate most of the time than to risk underestimation by a
factor of 1000 and more.
For now I'm turning off seqscans. This is a second time I got
terrible permormance with seqscans turned on because of bad
estimation. And my database will probably fit in cache.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
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: tometzky@batory.org.pl, tgl@sss.pgh.pa.us, pgsql-bugs@postgresql.org
Subject: Re: query very slow when enable_seqscan=on
In-Reply-To: <20060704083733.GA20102@batory.org.pl>
* 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