Why search term results different query plan?
Hi,
Could anybody give me hint, or reccomend a source about this:
Please note that two queries are different only in search terms (STAR and
A). Plan and performane difference between them confused me.
Thanks,
Erol
trollandtoad2=# explain SELECT DISTINCT p.* FROM product_t p,
product_detail_t pd WHERE ((pd.productid=p.productid) AND
(pd.productconditionid<>'PL' or pd.quantityonsite>0) AND pd.active=1) and
(p.Name LIKE '%STAR%' OR p.description LIKE '%STAR%' OR p.basesku LIKE
'%STAR%') order by p.name;
NOTICE: QUERY PLAN:
Unique (cost=1599.50..1599.58 rows=1 width=98)
-> Sort (cost=1599.50..1599.50 rows=2 width=98)
-> Nested Loop (cost=0.00..1599.48 rows=2 width=98)
-> Seq Scan on product_t p (cost=0.00..613.41 rows=1
width=94)
-> Seq Scan on product_detail_t pd (cost=0.00..983.19
rows=231 width=4)
[postgres@trollandtoad postgres]$ time psql -c "SELECT DISTINCT p.* FROM
product_t p, product_detail_t pd WHERE ((pd.productid=p.productid) AND
(pd.productconditionid<>'PL' or pd.quantityonsite>0) AND pd.active=1) and
(p.Name LIKE '%STAR%' OR p.description LIKE '%STAR%' OR p.basesku LIKE
'%STAR%') order by p.name;" -d trollandtoad2
real 4m24.500s
user 0m0.020s
sys 0m0.010s
------------------------------------------
EXPLAIN
trollandtoad2=# explain SELECT DISTINCT p.* FROM product_t p,
product_detail_t pd WHERE ((pd.productid=p.productid) AND
(pd.productconditionid<>'PL' or pd.quantityonsite>0) AND pd.active=1) and
(p.Name LIKE '%A%' OR p.description LIKE '%A%' OR p.basesku LIKE '%A%')
order by p.name;
NOTICE: QUERY PLAN:
Unique (cost=4456.37..5127.88 rows=1919 width=98)
-> Sort (cost=4456.37..4456.37 rows=19186 width=98)
-> Merge Join (cost=2389.21..2496.11 rows=19186 width=98)
-> Sort (cost=1396.97..1396.97 rows=8321 width=94)
-> Seq Scan on product_t p (cost=0.00..613.41
rows=8321 width=94)
-> Sort (cost=992.24..992.24 rows=231 width=4)
-> Seq Scan on product_detail_t pd (cost=0.00..983.19
rows=231 width=4)
EXPLAIN
[postgres@trollandtoad postgres]$ time psql -c "SELECT DISTINCT p.* FROM
product_t p, product_detail_t pd WHERE ((pd.productid=p.productid) AND
(pd.productconditionid<>'PL' or pd.quantityonsite>0) AND pd.active=1) and
(p.Name LIKE '%A%' OR p.description LIKE '%A%' OR p.basesku LIKE '%A%')
order by p.name;" -d trollandtoad2
real 0m6.284s
user 0m0.270s
sys 0m0.030s
On Sun, Sep 30, 2001 at 04:18:53AM +0300, Erol ?z wrote:
Hi,
Could anybody give me hint, or reccomend a source about this:
Please note that two queries are different only in search terms (STAR and
A). Plan and performane difference between them confused me.
Thanks,
Erol
%STAR%:
Unique (cost=1599.50..1599.58 rows=1 width=98)
-> Sort (cost=1599.50..1599.50 rows=2 width=98)
-> Nested Loop (cost=0.00..1599.48 rows=2 width=98)
-> Seq Scan on product_t p (cost=0.00..613.41 rows=1 width=94)
-> Seq Scan on product_detail_t pd (cost=0.00..983.19 rows=231 width=4)
%A%:
Unique (cost=4456.37..5127.88 rows=1919 width=98)
-> Sort (cost=4456.37..4456.37 rows=19186 width=98)
-> Merge Join (cost=2389.21..2496.11 rows=19186 width=98)
-> Sort (cost=1396.97..1396.97 rows=8321 width=94)
-> Seq Scan on product_t p (cost=0.00..613.41 rows=8321 width=94)
-> Sort (cost=992.24..992.24 rows=231 width=4)
-> Seq Scan on product_detail_t pd (cost=0.00..983.19 rows=231 width=4)
Note how in the first one it assumes that there is only one match in p
whereas in the second there are 8321. Are either of those correct?
I don't really see how you can do any real estimates on %X% type queries,
although maybe it's assuming longer string => less matches.
Not sure how to fix it though...
HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
"=?iso-8859-9?B?RXJvbCDWeg==?=" <eroloz@esg.com.tr> writes:
Could anybody give me hint, or reccomend a source about this:
Please note that two queries are different only in search terms (STAR and
A). Plan and performane difference between them confused me.
Note the difference in the estimated number of rows matched in
product_t:
-> Seq Scan on product_t p (cost=0.00..613.41 rows=1 width=94)
-> Seq Scan on product_t p (cost=0.00..613.41 rows=8321 width=94)
The planner thinks --- not unreasonably, IMHO --- that LIKE '%A%' will
match many more rows than LIKE '%STAR%'. Accordingly, it prepares
different plans for the two cases.
Since you're complaining, I suppose that LIKE '%STAR%' matches many more
rows than random chance would suggest, and so that plan turns out to be
poorly chosen.
regards, tom lane
Martijn van Oosterhout <kleptog@svana.org> writes:
I don't really see how you can do any real estimates on %X% type queries,
although maybe it's assuming longer string => less matches.
More exactly, it's assuming more fixed characters in the pattern ->
less matches; see like_selectivity in src/backend/utils/adt/selfuncs.c.
While the specific numbers it's using are made from whole cloth, I think
the principle should hold good.
I don't see any way to accumulate actual statistics that would improve
the estimate, do you?
regards, tom lane
On Sat, Sep 29, 2001 at 10:37:24PM -0400, Tom Lane wrote:
Martijn van Oosterhout <kleptog@svana.org> writes:
I don't really see how you can do any real estimates on %X% type queries,
although maybe it's assuming longer string => less matches.More exactly, it's assuming more fixed characters in the pattern ->
less matches; see like_selectivity in src/backend/utils/adt/selfuncs.c.
While the specific numbers it's using are made from whole cloth, I think
the principle should hold good.I don't see any way to accumulate actual statistics that would improve
the estimate, do you?
It is difficult, although obviously the results he is getting are bunk. The
only problem I can see is that it doesn't appear to be using any of the
gathered statistics at all. For example, if %STAR% matched the most common
value in the column, the selectivity would still be very low.
What I find most interesting about his case is that the query with %A% ran
40 times faster (260s to 6s) than the one where it thought only 1 row in p
was going to match. What that tells me is that the plan used for %A% is more
appropriate even though %STAR% is more selective.
How can you convince the planner of that?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.