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
