Re: select distinct, index not used

Thank you Tom. The cron job for vacuum+analyze was not installed on the host.
(I had this idea some seconds after posting)

After vacuum+analyze the performance is good. I am happy.

Nevertheless, on a different host with nearly the same data, a index scan is used.

foo_hostone_foo=# explain analyze SELECT DISTINCT "foo_abc_abc"."lieferant" FROM "foo_abc_abc";
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=15241.56..15820.71 rows=15 width=8) (actual time=1878.213..2393.550 rows=34 loops=1)
-> Sort (cost=15241.56..15531.13 rows=115830 width=8) (actual time=1878.207..2227.478 rows=115830 loops=1)
Sort Key: lieferant
-> Seq Scan on foo_abc_abc (cost=0.00..3518.30 rows=115830 width=8) (actual time=0.042..226.883 rows=115830
loops=1)
Total runtime: 2394.960 ms
(5 Zeilen)

foo_hostone_foo=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux)
(1 Zeile)

foo_hosttwo_foo=# explain analyze SELECT DISTINCT "foo_abc_abc"."lieferant" FROM "foo_abc_abc";
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..65641.70 rows=19 width=18) (actual time=0.163..1490.106 rows=68 loops=1)
-> Index Scan using foo_abc_abc_lieferant on foo_abc_abc (cost=0.00..64536.38 rows=442127 width=18) (actual
time=0.155..955.844 rows=227600 loops=1)
Total runtime: 1490.481 ms
(3 Zeilen)

foo_hosttwo_foo=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux)
(1 Zeile)

Tom Lane schrieb:
> Thomas Guettler <hv(at)tbz-pariv(dot)de> writes:
>> why does the statement take so long? The column 'lieferant' is indexed. But
>> a sequential scan gets done.
>
> It might have something to do with the fact that the planner's idea of
> the size of the table is off by a factor of more than 100:
>
>> -> Seq Scan on foo_abc_abc (cost=0.00..468944.11 rows=15404611 width=8) (actual time=0.029..125458.870 rows=115830 loops=1)
>
> You might need to review your vacuuming policy.
>
> (However, a full table indexscan isn't going to be particularly fast in
> any case; it's often the case that seqscan-and-sort is the right
> decision. I'm not sure this choice was wrong.)
>
> regards, tom lane
>

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de