From: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico Simonetti (Liveye) <federico@liveye.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance on ILIKE with AND operator...
Date: Mon, 10 Oct 2005 10:58:15 -0400
Message-ID: <20804.1128956295@sss.pgh.pa.us> (raw)
In-Reply-To: <434A6C2D.9070207@liveye.net>
References: <434A6C2D.9070207@liveye.net>
"Federico Simonetti (Liveye)" <federico@liveye.net> writes:
> I'm encountering a quite strange performance problem.
The problem stems from the horrid misestimation of the number of rows
fetched from detail0009:
> " -> Seq Scan on detail0009 (cost=0.00..20500.11
> rows=26 width=1005) (actual time=453.000..5983.000 rows=53588 loops=1)"
> " Filter: ((txcontenttype ~~* '%html%'::text)
> AND ((vchost)::text ~~* '%www.%'::text))"
When the planner is off by a factor of two thousand about the number of
rows involved, it's not very likely to produce a good plan :-(
In the OR case the rowcount estimate is 6334, which is somewhat closer
to reality (only about a factor of 10 off, looks like), and that changes
the plan to something that works acceptably well.
Assuming that this is web-log data, the prevalence of www and html
together is hardly surprising, but PG's statistical mechanisms will
never realize it. Not sure about a good workaround. Does it make
sense to combine the two conditions into one?
(vchost || txcontenttype) ilike '%www.%html%'
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, federico@liveye.net
Subject: Re: Query performance on ILIKE with AND operator...
In-Reply-To: <20804.1128956295@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