Some mystery with execution plans on postgres 8.1

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@rambler-co.ru   ICQ: 99-312-438
(910) 405-47-18