Josh,
I ran the vacuumdb as follows:
vacuumdb -f -v -e -a
and after that,
vacuumdb -z -v -e -a.
And now, the optimizer started to use a table scan and in consequence gives
me:
explain analyze select * from customer
where c_last = 'ROUGHTATION' and
c_w_id = 1 and
c_d_id = 1
order by c_w_id, c_d_id, c_last, c_first limit 1;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------
Limit (cost=6302.03..6302.03 rows=1 width=639) (actual time=208.33..208.33
rows=0 loops=1)
-> Sort (cost=6302.03..6302.04 rows=3 width=639) (actual time=208.32..208.32
rows=0 loops=1)
Sort Key: c_w_id, c_d_id, c_last, c_first
-> Seq Scan on customer (cost=0.00..6302.00 rows=3 width=639) (actual
time=207.99..207.99 rows=0 loops=1)
Filter: ((c_last = 'ROUGHTATION'::bpchar) AND (c_w_id = 1) AND (c_d_id = 1))
Total runtime: 208.54 msec
(6 rows)
When I force the index use a receive a better result:
set enable_seqscan to off;
explain analyze select * from customer
where c_last = 'ROUGHTATION' and
c_w_id = 1 and
c_d_id = 1
order by c_w_id, c_d_id, c_last, c_first limit 1;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------------------
Limit (cost=9860.03..9860.03 rows=1 width=639) (actual time=13.98..13.98
rows=0 loops=1)
-> Sort (cost=9860.03..9860.04 rows=3 width=639) (actual time=13.98..13.98
rows=0 loops=1)
Sort Key: c_w_id, c_d_id, c_last, c_first
-> Index Scan using pk_customer on customer (cost=0.00..9860.00 rows=3
width=639) (actual time=13.86..13.86 rows=0 loops=1)
Index Cond: ((c_w_id = 1) AND (c_d_id = 1))
Filter: (c_last = 'ROUGHTATION'::bpchar)
Total runtime: 14.11 msec
(7 rows)
Is this the only way to force the index ?
What are the reasons to the optimizer to decide for a worse plan ?
> Alfranio,
>
> > I'm a new PostgresSql user and I do not know so much about the
> > performance mechanisms currently implemented and available.
> <snip>
> > Does anybody know what is happening ?
>
> 90% likely: You haven't run VACUUM FULL ANALYZE in a while.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>