The idea of ad hoc indexes is speed up loop scans To prove my idea i
created a sql file in PGAdmin that creates the indexes on the fly then
runs the query then drops the indexs.
without the indexes it takes 18 to 19 seconds to run the query.
To create the index and do the query takes 400 milliseconds. I wish i
could do an Explain on it with create index in the query but i can't it
errors out. So i reran the query with indexes already in place it drops
the query time 191 milliseconds.
Create and deleting the indexes on the fly improves performance almost
50 times. I think creating Ad Hoc indexes on the fly in memory makes
sense . I imagine it would be even faster if the index stayed in memory
Tom Lane wrote:
Justin <[EMAIL PROTECTED]> writes:
Is there any plans in the future to add the ability for PostgreSQL to
create Ad Hoc indexes if it makes sense.
No, I'm not aware of anyone contemplating such a thing. I can hardly
imagine a situation where building an index for a single query is
actually a win. Maybe those DBMSes you mention were using this as a
substitute for having decent join mechanisms, or something?
regards, tom lane
"Sort (cost=4091.92..4091.93 rows=1 width=83) (actual
time=22003.849..22004.041 rows=458 loops=1)"
" Sort Key: to_char((coitem.coitem_scheddate)::timestamp with time zone,
'Month Dy DD YYYY'::text), cohead.cohead_number, coitem.coitem_linenumber"
" -> Nested Loop Left Join (cost=0.00..4091.91 rows=1 width=83) (actual
time=14.477..21998.119 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.54 rows=1 width=75) (actual
time=0.132..32.066 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.22 rows=1 width=44) (actual
time=0.121..25.253 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.94 rows=1 width=40)
(actual time=0.111..18.476 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.53 rows=1 width=30)
(actual time=0.100..11.662 rows=458 loops=1)"
" -> Index Scan using coitem_status_key on
coitem (cost=0.00..174.25 rows=1 width=30) (actual time=0.084..2.364 rows=458
loops=1)"
" Index Cond: (coitem_status =
'O'::bpchar)"
" Filter: ((coitem_scheddate >=
'2008-01-01'::date) AND (coitem_scheddate <= '2008-12-31'::date))"
" -> Index Scan using itemsite_pkey on itemsite
(cost=0.00..8.27 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=458)"
" Index Cond: (coitem.coitem_itemsite_id =
itemsite.itemsite_id)"
" -> Index Scan using item_pkey on item
(cost=0.00..0.40 rows=1 width=18) (actual time=0.008..0.010 rows=1 loops=458)"
" Index Cond: (itemsite.itemsite_item_id =
item.item_id)"
" -> Index Scan using cohead_pkey on cohead
(cost=0.00..8.27 rows=1 width=12) (actual time=0.008..0.010 rows=1 loops=458)"
" Index Cond: (cohead.cohead_id =
coitem.coitem_cohead_id)"
" -> Index Scan using cust_pkey on custinfo (cost=0.00..0.31
rows=1 width=39) (actual time=0.007..0.009 rows=1 loops=458)"
" Index Cond: (cohead.cohead_cust_id = custinfo.cust_id)"
" -> Index Scan using wo_pkey on wo (cost=0.00..8.27 rows=1 width=12)
(actual time=0.007..0.011 rows=1 loops=458)"
" Index Cond: (wo.wo_id = coitem.coitem_order_id)"
" SubPlan"
" -> Nested Loop (cost=958.43..1975.22 rows=1 width=36) (actual
time=22.607..22.955 rows=1 loops=419)"
" Join Filter: (wrkcnt.wrkcnt_id = wooper.wooper_wrkcnt_id)"
" InitPlan"
" -> Aggregate (cost=958.42..958.43 rows=1 width=4) (actual
time=13.870..13.870 rows=1 loops=419)"
" -> Seq Scan on wooper (cost=0.00..958.41 rows=1
width=4) (actual time=13.453..13.856 rows=1 loops=419)"
" Filter: (wooper_rncomplete AND (wooper_wo_id =
$0))"
" -> Seq Scan on wooper (cost=0.00..1013.10 rows=1 width=8)
(actual time=22.547..22.821 rows=1 loops=419)"
" Filter: ((wooper_seqnumber = $1) AND (wooper_wo_id =
$0))"
" -> Seq Scan on wrkcnt (cost=0.00..2.75 rows=75 width=36)
(actual time=0.010..0.052 rows=75 loops=419)"
" -> Aggregate (cost=958.42..958.43 rows=1 width=4) (actual
time=14.033..14.034 rows=1 loops=419)"
" -> Seq Scan on wooper (cost=0.00..958.41 rows=1 width=4)
(actual time=13.600..14.011 rows=1 loops=419)"
" Filter: (wooper_rncomplete AND (wooper_wo_id = $0))"
" -> Aggregate (cost=958.42..958.43 rows=1 width=4) (actual
time=14.058..14.059 rows=1 loops=458)"
" -> Seq Scan on wooper (cost=0.00..958.41 rows=1 width=4)
(actual time=13.671..14.043 rows=1 loops=458)"
" Filter: (wooper_rncomplete AND (wooper_wo_id = $0))"
"Total runtime: 22005.075 ms"
CREATE INDEX CONCURRENTLY coitem_scheddate_index
ON coitem
USING btree
(coitem_scheddate);
CREATE INDEX CONCURRENTLY wooper_wo_id_index
ON wooper
USING btree
(wooper_wo_id);
Select cohead_number, coitem_linenumber, round(coitem_qtyord, 0) as
coitem_qtyord, coitem_order_id, to_char(coitem_scheddate, 'Month Dy DD YYYY')
as coitem_scheddate, cust_name, cust_number,
item_number, cast(wo_number as text) ||'-' || cast(wo_subnumber as text) as
wo_number,
case
when (select max(wooper_seqnumber) from wooper where wooper_rncomplete
= true and wooper_wo_id = coitem_order_id) IS NULL
then 'No Operation Completed'
when (select max(wooper_seqnumber) from wooper where wooper_rncomplete
= true and wooper_wo_id = coitem_order_id) IS NOT NULL
then
(select wrkcnt_code || ' Operation Completed :' ||
cast(wooper_seqnumber as text) from wooper, wrkcnt
where wooper_seqnumber =(select max(wooper_seqnumber) from
wooper where wooper_rncomplete = true and wooper_wo_id = coitem_order_id)
and wooper_wo_id = coitem_order_id and wrkcnt_id =
wooper_wrkcnt_id )
end as LastFinshedOp
from coitem left join wo on wo_id = coitem.coitem_order_id, cohead, custinfo,
itemsite, item
where cohead_id = coitem_cohead_id and coitem_itemsite_id = itemsite_id and
itemsite_item_id = item_id
and cohead_cust_id = cust_id and coitem_status = 'O' and coitem_scheddate
between
'2008-01-01' and '2008-12-31'
order by coitem_scheddate, cohead_number, coitem_linenumber;
drop index coitem_scheddate_index;
drop index wooper_wo_id_index;"Sort (cost=459.19..459.19 rows=1 width=83) (actual time=117.026..117.217
rows=458 loops=1)"
" Sort Key: to_char((coitem.coitem_scheddate)::timestamp with time zone,
'Month Dy DD YYYY'::text), cohead.cohead_number, coitem.coitem_linenumber"
" -> Nested Loop Left Join (cost=0.00..459.18 rows=1 width=83) (actual
time=0.186..112.660 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.54 rows=1 width=75) (actual
time=0.125..18.115 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..191.22 rows=1 width=44) (actual
time=0.114..13.976 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.94 rows=1 width=40)
(actual time=0.105..10.142 rows=458 loops=1)"
" -> Nested Loop (cost=0.00..182.53 rows=1 width=30)
(actual time=0.094..5.974 rows=458 loops=1)"
" -> Index Scan using coitem_status_key on
coitem (cost=0.00..174.25 rows=1 width=30) (actual time=0.079..1.112 rows=458
loops=1)"
" Index Cond: (coitem_status =
'O'::bpchar)"
" Filter: ((coitem_scheddate >=
'2008-01-01'::date) AND (coitem_scheddate <= '2008-12-31'::date))"
" -> Index Scan using itemsite_pkey on itemsite
(cost=0.00..8.27 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=458)"
" Index Cond: (coitem.coitem_itemsite_id =
itemsite.itemsite_id)"
" -> Index Scan using item_pkey on item
(cost=0.00..0.40 rows=1 width=18) (actual time=0.005..0.006 rows=1 loops=458)"
" Index Cond: (itemsite.itemsite_item_id =
item.item_id)"
" -> Index Scan using cohead_pkey on cohead
(cost=0.00..8.27 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=458)"
" Index Cond: (cohead.cohead_id =
coitem.coitem_cohead_id)"
" -> Index Scan using cust_pkey on custinfo (cost=0.00..0.31
rows=1 width=39) (actual time=0.004..0.005 rows=1 loops=458)"
" Index Cond: (cohead.cohead_cust_id = custinfo.cust_id)"
" -> Index Scan using wo_pkey on wo (cost=0.00..8.27 rows=1 width=12)
(actual time=0.004..0.005 rows=1 loops=458)"
" Index Cond: (wo.wo_id = coitem.coitem_order_id)"
" SubPlan"
" -> Nested Loop (cost=63.90..131.53 rows=1 width=36) (actual
time=0.067..0.137 rows=1 loops=419)"
" Join Filter: (wrkcnt.wrkcnt_id = wooper.wooper_wrkcnt_id)"
" InitPlan"
" -> Aggregate (cost=63.89..63.90 rows=1 width=4) (actual
time=0.024..0.025 rows=1 loops=419)"
" -> Index Scan using wooper_wo_id_index on wooper
(cost=0.00..63.89 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=419)"
" Index Cond: (wooper_wo_id = $0)"
" Filter: wooper_rncomplete"
" -> Index Scan using wooper_wo_id_index on wooper
(cost=0.00..63.94 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=419)"
" Index Cond: (wooper_wo_id = $0)"
" Filter: (wooper_seqnumber = $1)"
" -> Seq Scan on wrkcnt (cost=0.00..2.75 rows=75 width=36)
(actual time=0.003..0.041 rows=75 loops=419)"
" -> Aggregate (cost=63.89..63.90 rows=1 width=4) (actual
time=0.025..0.026 rows=1 loops=419)"
" -> Index Scan using wooper_wo_id_index on wooper
(cost=0.00..63.89 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=419)"
" Index Cond: (wooper_wo_id = $0)"
" Filter: wooper_rncomplete"
" -> Aggregate (cost=63.89..63.90 rows=1 width=4) (actual
time=0.028..0.029 rows=1 loops=458)"
" -> Index Scan using wooper_wo_id_index on wooper
(cost=0.00..63.89 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=458)"
" Index Cond: (wooper_wo_id = $0)"
" Filter: wooper_rncomplete"
"Total runtime: 118.150 ms"
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
