Обсуждение: Timestamp index not used in some cases
I have the following table:
CREATE TABLE "temp".tmp_135528
(
id integer NOT NULL,
prid integer,
group_id integer,
iinv integer,
oinv integer,
isum numeric,
osum numeric,
idate timestamp without time zone,
odate timestamp without time zone,
CONSTRAINT t_135528_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
With index:
CREATE INDEX t_135528
ON "temp".tmp_135528
USING btree
(idate, group_id, osum, oinv);
When the following query is executed the index is not used:
EXPLAIN SELECT id, osum
FROM temp.tmp_135528
WHERE idate <= '2007-05-17 00:00:00'::timestamp
AND group_id = '13'
AND osum <= '19654.45328'
AND oinv = -1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tmp_135528 (cost=0.00..7022.36 rows=1166 width=11)
Filter: ((idate <= '2007-05-17 00:00:00'::timestamp without time zone) AND (osum <= 19654.45328) AND (group_id = 13) AND (oinv = (-1)))
(2 rows)
When
"idate <= '2007-05-17 00:00:00'::timestamp"
is changed to
"idate >= '2007-05-17 00:00:00'::timestamp"
or
"idate = '2007-05-17 00:00:00'::timestamp"
then the index is used:
EXPLAIN SELECT id, osum
FROM temp.tmp_135528
WHERE idate >= '2007-05-17 00:00:00'::timestamp
AND group_id = '13'
AND osum <= '19654.45328'
AND oinv = -1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using t_135528 on tmp_135528 (cost=0.00..462.61 rows=47 width=11)
Index Cond: ((idate >= '2007-05-17 00:00:00'::timestamp without time zone) AND (group_id = 13) AND (osum <= 19654.45328) AND (oinv = (-1)))
(2 rows)
Why I cannot use the index in <= comparison on timestamp ?
Best regards,
Evgeni Vasilev
JAR Computers
IT Department
jabber id: evasilev@jabber.jarcomputers.com
On Tue, May 12, 2009 at 3:00 AM, Евгений Василев <evasilev@jarcomputers.com> wrote: > I have the following table: > > CREATE TABLE "temp".tmp_135528 > ( > id integer NOT NULL, > prid integer, > group_id integer, > iinv integer, > oinv integer, > isum numeric, > osum numeric, > idate timestamp without time zone, > odate timestamp without time zone, > CONSTRAINT t_135528_pk PRIMARY KEY (id) > ) > WITH (OIDS=FALSE); > > With index: > > CREATE INDEX t_135528 > ON "temp".tmp_135528 > USING btree > (idate, group_id, osum, oinv); > > When the following query is executed the index is not used: > > EXPLAIN SELECT id, osum > FROM temp.tmp_135528 > WHERE idate <= '2007-05-17 00:00:00'::timestamp > AND group_id = '13' > AND osum <= '19654.45328' > AND oinv = -1 > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on tmp_135528 (cost=0.00..7022.36 rows=1166 width=11) > Filter: ((idate <= '2007-05-17 00:00:00'::timestamp without time zone) AND > (osum <= 19654.45328) AND (group_id = 13) AND (oinv = (-1))) > (2 rows) > > When > "idate <= '2007-05-17 00:00:00'::timestamp" > is changed to > "idate >= '2007-05-17 00:00:00'::timestamp" > or > "idate = '2007-05-17 00:00:00'::timestamp" > then the index is used: > > EXPLAIN SELECT id, osum > FROM temp.tmp_135528 > WHERE idate >= '2007-05-17 00:00:00'::timestamp > AND group_id = '13' > AND osum <= '19654.45328' > AND oinv = -1; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------- > Index Scan using t_135528 on tmp_135528 (cost=0.00..462.61 rows=47 width=11) > Index Cond: ((idate >= '2007-05-17 00:00:00'::timestamp without time zone) > AND (group_id = 13) AND (osum <= 19654.45328) AND (oinv = (-1))) > (2 rows) > > Why I cannot use the index in <= comparison on timestamp ? You can. But in this instance one query is returning 47 rows while the other is returning 1166 rows (or the planner thinks it is). There's a switchover point where it's cheaper to seq scan. You can adjust this point up and down by adjusting various costs parameters. random_page_cost is commonly lowered to the 1.5 to 2.0 range, and effective_cache_size is normally set higher, to match the cache in the kernel plus the shared_buffer size.
On Tuesday 12 May 2009 12:55:14 Scott Marlowe wrote: > On Tue, May 12, 2009 at 3:00 AM, Евгений Василев > > <evasilev@jarcomputers.com> wrote: > > I have the following table: > > > > CREATE TABLE "temp".tmp_135528 > > ( > > id integer NOT NULL, > > prid integer, > > group_id integer, > > iinv integer, > > oinv integer, > > isum numeric, > > osum numeric, > > idate timestamp without time zone, > > odate timestamp without time zone, > > CONSTRAINT t_135528_pk PRIMARY KEY (id) > > ) > > WITH (OIDS=FALSE); > > > > With index: > > > > CREATE INDEX t_135528 > > ON "temp".tmp_135528 > > USING btree > > (idate, group_id, osum, oinv); > > > > When the following query is executed the index is not used: > > > > EXPLAIN SELECT id, osum > > FROM temp.tmp_135528 > > WHERE idate <= '2007-05-17 00:00:00'::timestamp > > AND group_id = '13' > > AND osum <= '19654.45328' > > AND oinv = -1 > > > > QUERY PLAN > > ------------------------------------------------------------------------- > >------------------------------------------------------------------ Seq > > Scan on tmp_135528 (cost=0.00..7022.36 rows=1166 width=11) > > Filter: ((idate <= '2007-05-17 00:00:00'::timestamp without time zone) > > AND (osum <= 19654.45328) AND (group_id = 13) AND (oinv = (-1))) > > (2 rows) > > > > When > > "idate <= '2007-05-17 00:00:00'::timestamp" > > is changed to > > "idate >= '2007-05-17 00:00:00'::timestamp" > > or > > "idate = '2007-05-17 00:00:00'::timestamp" > > then the index is used: > > > > EXPLAIN SELECT id, osum > > FROM temp.tmp_135528 > > WHERE idate >= '2007-05-17 00:00:00'::timestamp > > AND group_id = '13' > > AND osum <= '19654.45328' > > AND oinv = -1; > > QUERY PLAN > > ------------------------------------------------------------------------- > >---------------------------------------------------------------------- > > Index Scan using t_135528 on tmp_135528 (cost=0.00..462.61 rows=47 > > width=11) Index Cond: ((idate >= '2007-05-17 00:00:00'::timestamp without > > time zone) AND (group_id = 13) AND (osum <= 19654.45328) AND (oinv = > > (-1))) (2 rows) > > > > Why I cannot use the index in <= comparison on timestamp ? > > You can. But in this instance one query is returning 47 rows while > the other is returning 1166 rows (or the planner thinks it is). > There's a switchover point where it's cheaper to seq scan. You can > adjust this point up and down by adjusting various costs parameters. > random_page_cost is commonly lowered to the 1.5 to 2.0 range, and > effective_cache_size is normally set higher, to match the cache in the > kernel plus the shared_buffer size. Thank you this worked like a charm.
Hi all,
The query below is fairly fast if the commented sub-select is
commented, but once I included that column, it takes over 10 minutes to
return results. Can someone shed some light on it? I was able to redo
the query using left joins instead, and it only marginally increased
result time. This is an application (Quasar by Linux Canada) I can't
change the query in, so want to see if there's a way to tune the
database for it to perform faster. Application developer says that
Sybase is able to run this same query with the price column included
with only marginal increase in time.
select item.item_id,item_plu.number,item.description,
(select dept.name from dept where dept.dept_id = item.dept_id)
-- ,(select price from item_price
-- where item_price.item_id = item.item_id
-- and item_price.zone_id = 'OUsEaRcAA3jQrg42WHUm8A'
-- and item_price.price_type = 0
-- and item_price.size_name = item.sell_size)
from item join item_plu on item.item_id = item_plu.item_id and
item_plu.seq_num = 0
where item.inactive_on is null and exists (select item_num.number from
item_num
where item_num.item_id = item.item_id)
and exists (select stocked from item_store where stocked = 'Y'
and item_store.item_id = item.item_id)
Explain analyze without price column:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1563.82..13922.00 rows=10659 width=102) (actual
time=165.988..386.737 rows=10669 loops=1)
Hash Cond: (item.item_id = item_store.item_id)
-> Hash Join (cost=1164.70..2530.78 rows=10659 width=148) (actual
time=129.804..222.008 rows=10669 loops=1)
Hash Cond: (item.item_id = item_plu.item_id)
-> Hash Join (cost=626.65..1792.86 rows=10661 width=93)
(actual time=92.930..149.267 rows=10669 loops=1)
Hash Cond: (item.item_id = item_num.item_id)
-> Seq Scan on item (cost=0.00..882.67 rows=10665
width=70) (actual time=0.006..17.706 rows=10669 loops=1)
Filter: (inactive_on IS NULL)
-> Hash (cost=493.39..493.39 rows=10661 width=23)
(actual time=92.872..92.872 rows=10672 loops=1)
-> HashAggregate (cost=386.78..493.39 rows=10661
width=23) (actual time=59.193..75.303 rows=10672 loops=1)
-> Seq Scan on item_num (cost=0.00..339.22
rows=19022 width=23) (actual time=0.007..26.013 rows=19040 loops=1)
-> Hash (cost=404.76..404.76 rows=10663 width=55) (actual
time=36.835..36.835 rows=10672 loops=1)
-> Seq Scan on item_plu (cost=0.00..404.76 rows=10663
width=55) (actual time=0.010..18.609 rows=10672 loops=1)
Filter: (seq_num = 0)
-> Hash (cost=265.56..265.56 rows=10685 width=23) (actual
time=36.123..36.123 rows=10672 loops=1)
-> Seq Scan on item_store (cost=0.00..265.56 rows=10685
width=23) (actual time=0.015..17.959 rows=10672 loops=1)
Filter: (stocked = 'Y'::bpchar)
SubPlan 1
-> Seq Scan on dept (cost=0.00..1.01 rows=1 width=32) (actual
time=0.002..0.004 rows=1 loops=10669)
Filter: (dept_id = $0)
Total runtime: 401.560 ms
(21 rows)
Explain with price column:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1563.82..4525876.70 rows=10659 width=106) (actual
time=171.186..20863.887 rows=10669 loops=1)
Hash Cond: (item.item_id = item_store.item_id)
-> Hash Join (cost=1164.70..2530.78 rows=10659 width=152) (actual
time=130.025..236.528 rows=10669 loops=1)
Hash Cond: (item.item_id = item_plu.item_id)
-> Hash Join (cost=626.65..1792.86 rows=10661 width=97)
(actual time=92.780..158.514 rows=10669 loops=1)
Hash Cond: (item.item_id = item_num.item_id)
-> Seq Scan on item (cost=0.00..882.67 rows=10665
width=74) (actual time=0.008..18.836 rows=10669 loops=1)
Filter: (inactive_on IS NULL)
-> Hash (cost=493.39..493.39 rows=10661 width=23)
(actual time=92.727..92.727 rows=10672 loops=1)
-> HashAggregate (cost=386.78..493.39 rows=10661
width=23) (actual time=59.064..75.243 rows=10672 loops=1)
-> Seq Scan on item_num (cost=0.00..339.22
rows=19022 width=23) (actual time=0.009..26.287 rows=19040 loops=1)
-> Hash (cost=404.76..404.76 rows=10663 width=55) (actual
time=37.206..37.206 rows=10672 loops=1)
-> Seq Scan on item_plu (cost=0.00..404.76 rows=10663
width=55) (actual time=0.011..18.823 rows=10672 loops=1)
Filter: (seq_num = 0)
-> Hash (cost=265.56..265.56 rows=10685 width=23) (actual
time=36.395..36.395 rows=10672 loops=1)
-> Seq Scan on item_store (cost=0.00..265.56 rows=10685
width=23) (actual time=0.015..18.120 rows=10672 loops=1)
Filter: (stocked = 'Y'::bpchar)
SubPlan 1
-> Seq Scan on dept (cost=0.00..1.01 rows=1 width=32) (actual
time=0.002..0.004 rows=1 loops=10669)
Filter: (dept_id = $0)
SubPlan 2
-> Seq Scan on item_price (cost=0.00..423.30 rows=1 width=8)
(actual time=1.914..1.914 rows=0 loops=10669)
Filter: ((item_id = $1) AND (zone_id =
'OUsEaRcAA3jQrg42WHUm8A'::bpchar) AND (price_type = 0) AND
((size_name)::text = ($2)::text))
Total runtime: 20879.388 ms
(24 rows)