If short: adding index to table change execution plans on query which dont
use new index.
More detailed info:
tv2=# \d tv_events
Table "public.tv_events"
Column | Type |
Modifiers
------------+-----------------------------+---------------------------------
--------------------------------------
id | integer | not null default
nextval(('public.documents_id_seq'::text)::regclass)
status | smallint | not null default 0
name | character varying(255) |
ext_id | integer |
start | timestamp without time zone |
finish | timestamp without time zone |
star | integer |
flag_id | integer |
flag2_id | integer |
channel_id | integer |
Indexes:
"tv_events_pkey" PRIMARY KEY, btree (id)
"tv_events_main3_idx" btree (flag_id, "start", finish)
"tv_events_main_idx" btree (channel_id, "start", finish)
"tv_events_start_finish" btree ("start", finish)
tv2=# select count(*) from tv_events;
count
-------
30353
(1 row)
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish <=
'2006-06-09 06:00' AND start >= '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------
Sort (cost=862.48..864.12 rows=656 width=522) (actual time=6.913..7.684
rows=226 loops=1)
Sort Key: "start"
-> Bitmap Heap Scan on tv_events d (cost=16.34..831.79 rows=656
width=522) (actual time=1.530..4.642 rows=226 loops=1)
Recheck Cond: ((flag_id = 5) AND ("start" >= '2006-06-08
06:00:00'::timestamp without time zone) AND (finish <= '2006-06-09
06:00:00'::timestamp without time zone))
Filter: (("class")::text = 'tv2::Event'::text)
-> Bitmap Index Scan on tv_events_main3_idx (cost=0.00..16.34
rows=656 width=0) (actual time=1.366..1.366 rows=678 loops=1)
Index Cond: ((flag_id = 5) AND ("start" >= '2006-06-08
06:00:00'::timestamp without time zone) AND (finish <= '2006-06-09
06:00:00'::timestamp without time zone))
Total runtime: 8.657 ms
(8 rows)
Ok here used Bitmap Index Scan on tv_events_main3_idx
Lets now add one more index:
tv2=# CREATE INDEX test_idx on tv_events (flag_id,start);
CREATE INDEX
tv2=# ANALYZE tv_events;
ANALYZE
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish <=
'2006-06-09 06:00' AND start >= '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------
Index Scan using tv_events_main3_idx on tv_events d (cost=0.00..919.02
rows=656 width=522) (actual time=0.137..3.907 rows=226 loops=1)
Index Cond: ((flag_id = 5) AND ("start" >= '2006-06-08
06:00:00'::timestamp without time zone) AND (finish <= '2006-06-09
06:00:00'::timestamp without time zone))
Filter: (("class")::text = 'tv2::Event'::text)
Total runtime: 4.746 ms
(4 rows)
Now plan changed to more optimal and fast. But plan NOT using new index
test_idx...
Lets drop new test index again:
tv2=# drop INDEX test_idx;
DROP INDEX
tv2=# ANALYZE tv_events;
ANALYZE
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish <=
'2006-06-09 06:00' AND start >= '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------
Sort (cost=862.48..864.12 rows=656 width=522) (actual time=5.754..6.522
rows=226 loops=1)
Sort Key: "start"
-> Bitmap Heap Scan on tv_events d (cost=16.34..831.79 rows=656
width=522) (actual time=0.952..3.584 rows=226 loops=1)
Recheck Cond: ((flag_id = 5) AND ("start" >= '2006-06-08
06:00:00'::timestamp without time zone) AND (finish <= '2006-06-09
06:00:00'::timestamp without time zone))
Filter: (("class")::text = 'tv2::Event'::text)
-> Bitmap Index Scan on tv_events_main3_idx (cost=0.00..16.34
rows=656 width=0) (actual time=0.788..0.788 rows=678 loops=1)
Index Cond: ((flag_id = 5) AND ("start" >= '2006-06-08
06:00:00'::timestamp without time zone) AND (finish <= '2006-06-09
06:00:00'::timestamp without time zone))
Total runtime: 7.449 ms
(8 rows)
And plan returned to old not so optimal one...
So adding/dropping index not used in query execute plan can change that plan
somehow.
I completly lost... that first time in my life (4 year active postgres
admin/developer) when i cannot understand way how to indexe's and planner
work.
Any ideas?
That situation 100% reproduceable on my system (not depend postgres
stop/start... server reload etc)/
PS: sorry for bad english.
====================================================================
SY Maxim Boguk
astar@xxxxxxxxxxxxx ICQ: 99-312-438
(910) 405-47-18
