From: Tom Lane <tgl@sss.pgh.pa.us>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] why is postgres estimating so badly?
Date: Wed, 17 Jul 2002 13:43:50 -0400
Message-ID: <15637.1026927830@sss.pgh.pa.us> (raw)
In-Reply-To: <01b901c22d7b$2e9e9f90$cab990c1@atc.unican.es>
References: <01b901c22d7b$2e9e9f90$cab990c1@atc.unican.es>
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
> -> Seq Scan on part (cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1)
Seems like the major misestimation is above: the LIKE clause on part is
estimated to select just one row, but it selects 10856 of 'em. Had the
planner realized the number of returned rows would be in the thousands,
it'd likely have used quite a different plan structure.
> AND part.name LIKE '%green%'
It's difficult for the planner to produce a decent estimate for the
selectivity of an unanchored LIKE clause, since there are no statistics
it can use for the purpose. We recently changed FIXED_CHAR_SEL in
src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make
this particular case come out better. (I believe the estimate would
work out to about 320, if part is 200K rows; that should be enough to
produce at least some change of plan.) You could try patching your
local installation likewise.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, lamigo@atc.unican.es, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] why is postgres estimating so badly?
In-Reply-To: <15637.1026927830@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