Re: IN + index

* A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> schrieb:

> Und ein explain fehlt noch immer...

Okay, sei hier nachgeliefert. Übrigends ist mir dabei grad
aufgefallen, daß wirklich nicht immer die Indizes benutzt werden:

Hier erstmal die Table:

Table "public.signale"
Column | Type | Modifiers
--------------+-----------------------------+----------------------------------------------------------
sid | bigint | not null default (nextval('signale_sid'::text))::integer
ordertype | character varying(3) | not null
fillprice | real | not null
timefilled | timestamp with time zone | not null
signal | character varying | not null
wid | integer | not null
wsid | integer | not null
parameter | character varying | not null
writetime | timestamp with time zone |
source | character(1) |
minprice | real |
maxprice | real |
currenttime | timestamp without time zone |
currentprice | real |
gelesen | boolean | default false
lot | integer | default 1
broker_order | text | default broker.broker_order()
trade_id | oid |
pipcost | real |
bypass | boolean | default false
Indexes:
"signale_sid_pkey" primary key, btree (sid)
"signale_ui1" unique, btree (((sid)::oid))
"signale_ui3" unique, btree (((sid)::integer))
"signale_index_wid_wsid" btree (wid, wsid)
"signale_si1" btree (timefilled)
"signale_si2" btree (((wid)::oid), ((wsid)::oid))
"signale_si3" btree ((((ordertype)::text ~~ '%E'::text)))
"signale_si4" btree ((((ordertype)::text ~~ '%X'::text)))
"signale_trade_id" btree (trade_id)
Rules:
r_signale_delete AS ON DELETE TO signale DO (DELETE FROM signale_limits WHERE (signale_limits.sid = old.sid); DELETE FROM capital.open_trade WHERE (open_trade.open_signal_id = (old.sid)::oid); DELETE FROM signale_trade WHERE (signale_trade.sid = old.sid); )
r_signale_update AS ON UPDATE TO signale DO (UPDATE capital.open_trade SET open_rate = COALESCE(new.currentprice, new.fillprice), open_timestamp = COALESCE((new.currenttime)::timestamp with time zone, new.timefilled), passel = new.lot, open_confirmed = new.gelesen WHERE (open_trade.open_signal_id = (new.sid)::oid); UPDATE capital.open_trade SET close_rate = COALESCE(new.currentprice, new.fillprice), close_timestamp = COALESCE((new.currenttime)::timestamp with time zone, new.timefilled), close_confirmed = new.gelesen WHERE (open_trade.close_signal_id = (new.sid)::oid); )
Triggers:
tr_signale AFTER INSERT ON signale FOR EACH ROW EXECUTE PROCEDURE insert_signale_limits_send_verarbeitung()

Und nun mal diverse EXPLAIN's:

fxignal_capital=# explain SELECT * from signale where sid = 1 ;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on signale (cost=0.00..973.02 rows=2 width=153)
Filter: (sid = 1)

fxignal_capital=# explain SELECT * from signale where sid = '1' ;
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using signale_sid_pkey on signale (cost=0.00..4.09 rows=2 width=153)
Index Cond: (sid = 1::bigint)

fxignal_capital=# explain SELECT * from signale where sid = 1::oid ;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on signale (cost=0.00..1044.23 rows=143 width=153)
Filter: ((sid)::oid = 1::oid)

fxignal_capital=# explain SELECT * from signale where sid IN ( '1'::bigint, '2', '3' ) ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using signale_sid_pkey, signale_sid_pkey, signale_sid_pkey on signale (cost=0.00..12.29 rows=3 width=153)
Index Cond: ((sid = 1::bigint) OR (sid = 2::bigint) OR (sid = 3::bigint))

fxignal_capital=# explain SELECT * from signale where sid IN ( '1'::bigint, 2, '3' ) ;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on signale (cost=0.00..1115.43 rows=3 width=153)
Filter: ((sid = 1::bigint) OR (sid = 2) OR (sid = 3::bigint))

Ergo: IN wird (zumindest bei fester Menge) durch OR ersetzt.
Woher der Performance-Schub kam weiß ich nicht.

Allerdings werden offenbar keine CAST'ed Indizes verwendet.
Warum ?!

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgreSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------