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)
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)