Re: simple query runs 26 seconds

"Andrus" <kobruleht2(at)hot(dot)ee> writes:
>> The time seems entirely spent in fetching rows from table "rid".
>> Perhaps that table is bloated by lack of vacuuming --- can you
>> show the output from "vacuum verbose rid"?

OK, so the info relevant to this query is

> INFO: index "rid_toode_idx" now contains 1517900 row versions in 9950 pages
> DETAIL: 7375 index row versions were removed.
> 245 index pages have been deleted, 232 are currently reusable.

> INFO: "rid": found 7375 removable, 1517900 nonremovable row versions in
> 82560 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 292585 unused item pointers.
> 18375 pages contain useful free space.
> 0 pages are entirely empty.

which gives us a density of about 150 entries/page in the index and
18 rows/page in the heap proper. That seems a bit low --- do you think
your rows are several hundred bytes each? If not, the best cleanup
strategy is probably to CLUSTER the table on whichever index you use
most (dunno if this is your most important query or not).

> postgresql.conf contains
> autovacuum = on # enable autovacuum subprocess?
> However, log files does not show any autovacuum messages.
> So I expect that autovacuum in not running.
> Any idea why autovacuum is not running ?

Did you also turn on stats_row_level and stats_start_collector?

It could also be that autovac *is* running but its efforts are wasted
because of too small FSM settings --- what have you got max_fsm_pages
set to?

regards, tom lane