Re: performance; disk bad or something? - Mailing list pgsql-general

Tom Lane skrev:
> Marcus Engene <mengpg2@engene.se> writes:
>
>>          ->  Index Scan using apa_item_common_x1 on apa_item_common aic
>> (cost=0.00..4956.68 rows=1174 width=8) (actual time=19.854..9557.606
>> rows=1226 loops=1)
>>
>
> If the table only has 12000 rows then it should never have used an index
> scan here at all --- a plain seqscan is usually the best bet for
> retrieving 10% of a table.  Are you using nondefault planner settings?
>
> How big is the table physically (VACUUM VERBOSE output about it might
> tell something)?
Hi and thanks for your answer!

All planner settings in postgresql.conf are commented out. Until
yesterday the only setting I've poked with is shared_buffers.

Best regards,
Marcus

apa=> vacuum verbose apa_item_common;
INFO:  vacuuming "public.apa_item_common"
INFO:  index "apa_item_common_pkey" now contains 12863 row versions in
36 pages
DETAIL:  1246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.21 sec.
INFO:  index "apa_item_common_x1" now contains 12863 row versions in 38
pages
DETAIL:  1246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.72 sec.
INFO:  index "apa_item_common_fts" now contains 12863 row versions in
391 pages
DETAIL:  1246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.01u sec elapsed 1.16 sec.
INFO:  index "apa_item_common_x2" now contains 12863 row versions in 36
pages
DETAIL:  1246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.51 sec.
INFO:  "apa_item_common": removed 9028 row versions in 3651 pages
DETAIL:  CPU 0.24s/0.36u sec elapsed 30.69 sec.
INFO:  "apa_item_common": found 9028 removable, 12863 nonremovable row
versions in 14489 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 76646 unused item pointers.
0 pages are entirely empty.
CPU 0.64s/0.47u sec elapsed 84.91 sec.
INFO:  vacuuming "pg_toast.pg_toast_181470"
INFO:  index "pg_toast_181470_index" now contains 1040 row versions in 5
pages
DETAIL:  71 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO:  "pg_toast_181470": removed 618 row versions in 383 pages
DETAIL:  CPU 0.01s/0.03u sec elapsed 4.55 sec.
INFO:  "pg_toast_181470": found 618 removable, 1040 nonremovable row
versions in 1288 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 6121 unused item pointers.
0 pages are entirely empty.
CPU 0.03s/0.04u sec elapsed 16.64 sec.
VACUUM
apa=>