Plan changes from index scan to seq scan after 5 executions

[Alexander Kukushkin <cyberdemn@xxxxxxxxx>]

Hi,

Recently I've been investigating a strange behavior of one stored procedure.

According to the statistics its execution time was very high (15 seconds), but if I run the same statement from console it was very fast, just a few milliseconds.

At the end I was able to prepare a short script, which reproduces the problem:

localhost/postgres=# create table test(id serial not null primary key, value text);         
CREATE TABLE
localhost/postgres=# create index ON test(id) where value = 'l';                                
CREATE INDEX
localhost/postgres=# insert into test(value) select 'h' FROM generate_series(1, 1000000);
INSERT 0 1000000
localhost/postgres=# analyze test;
ANALYZE
localhost/postgres=# prepare foo as select * FROM test where value = $1 limit 1;
PREPARE

Now we will run prepared statement. First 5 times it will produce following output:
localhost/postgres=# explain analyze execute foo('l');
                                                       QUERY PLAN                                                      
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Limit  (cost=0.12..4.14 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=1)
   ->  Index Scan using test_id_idx on test  (cost=0.12..4.14 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=1)
 Planning time: 0.188 ms
 Execution time: 0.014 ms
(4 rows)

But on the 6th time (and further) plan it starts using Seq Scan:
localhost/postgres=# explain analyze execute foo('l');
                                                   QUERY PLAN                                                  
────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Limit  (cost=0.00..0.02 rows=1 width=6) (actual time=77.890..77.890 rows=0 loops=1)
   ->  Seq Scan on test  (cost=0.00..16925.00 rows=1000000 width=6) (actual time=77.889..77.889 rows=0 loops=1)
         Filter: (value = $1)
         Rows Removed by Filter: 1000000
 Planning time: 0.104 ms
 Execution time: 77.904 ms
(6 rows)

If I remove "LIMIT 1" from the query, everything is good, it will always use Index Scan.

Regards,
--

Alexander Kukushkin