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