Re: PG8.2.1 choosing slow seqscan over idx scan

On 17-Jan-07, at 9:37 AM, Jeremy Haile wrote:

>> I still keep wondering if this table is bloated with dead tuples.
>> Even
>> if you vacuum often if there's a connection with an idle transaction,
>> the tuples can't be reclaimed and the table would continue to grow.
>
> I used to vacuum once an hour, although I've switched it to autovacuum
> now. It definitely could be bloated with dead tuples. I'll paste the
> "vacuum analyze verbose" output at the bottom of this e-mail. Would a
> vacuum full be a good idea?
>
>
>> Assuming the table's NOT bloated, you may do well to increase the
>> effective_cache_size, which doesn't allocate anything,
> <snip>
>> try setting it to something like 512MB or so.
>
> It's currently set to 1000MB.

How much memory does the box have

>
>
>> If your table is bloating, and you don't have idle transactions
>> hanging
>> of the database, it could be that your fsm settings are too low.
>
> fsm is currently set to 2000000. Is there any harm in setting it too
> high? =)

Yes, it takes up space
>
> Here's the vacuum analyze verbose output:
>
> INFO: vacuuming "public.transaction_facts"
> INFO: scanned index "transaction_facts_pkey" to remove 759969 row
> versions
> DETAIL: CPU 7.20s/2.31u sec elapsed 315.31 sec.
> INFO: scanned index "transaction_facts_dcms_dim_id_idx" to remove
> 759969 row versions
> DETAIL: CPU 1.29s/2.15u sec elapsed 146.98 sec.
> INFO: scanned index "transaction_facts_merchant_dim_id_idx" to remove
> 759969 row versions
> DETAIL: CPU 1.10s/2.10u sec elapsed 126.09 sec.
> INFO: scanned index "transaction_facts_transaction_date_idx" to
> remove
> 759969 row versions
> DETAIL: CPU 1.65s/2.40u sec elapsed 259.25 sec.
> INFO: scanned index "transaction_facts_transaction_id_idx" to remove
> 759969 row versions
> DETAIL: CPU 7.48s/2.85u sec elapsed 371.98 sec.
> INFO: scanned index "transaction_facts_product_date_idx" to remove
> 759969 row versions
> DETAIL: CPU 2.32s/2.10u sec elapsed 303.83 sec.
> INFO: scanned index "transaction_facts_merchant_product_date_idx" to
> remove 759969 row versions
> DETAIL: CPU 2.48s/2.31u sec elapsed 295.19 sec.
> INFO: scanned index "transaction_facts_merchant_date_idx" to remove
> 759969 row versions
> DETAIL: CPU 8.10s/3.35u sec elapsed 398.73 sec.
> INFO: scanned index "transaction_facts_success_idx" to remove 759969
> row versions
> DETAIL: CPU 5.01s/2.84u sec elapsed 192.73 sec.
> INFO: scanned index "transaction_facts_failed_idx" to remove
> 759969 row
> versions
> DETAIL: CPU 1.03s/1.90u sec elapsed 123.00 sec.
> INFO: scanned index "transaction_facts_archived_idx" to remove 759969
> row versions
> DETAIL: CPU 1.03s/1.39u sec elapsed 104.42 sec.
> INFO: scanned index "transaction_facts_response_code_idx" to remove
> 759969 row versions
> DETAIL: CPU 0.75s/2.17u sec elapsed 36.71 sec.
> INFO: scanned index "transaction_facts_transaction_source_idx" to
> remove 759969 row versions
> DETAIL: CPU 0.60s/1.75u sec elapsed 42.29 sec.
> INFO: scanned index "transaction_facts_transaction_id_source_idx" to
> remove 759969 row versions
> DETAIL: CPU 1.14s/1.84u sec elapsed 44.75 sec.
> INFO: "transaction_facts": removed 759969 row versions in 14360 pages
> DETAIL: CPU 0.57s/0.23u sec elapsed 45.28 sec.
> INFO: index "transaction_facts_pkey" now contains 2274280 row
> versions
> in 152872 pages
> DETAIL: 759969 index row versions were removed.
> 134813 index pages have been deleted, 134813 are currently reusable.
> CPU 0.00s/0.01u sec elapsed 0.01 sec.
> INFO: index "transaction_facts_dcms_dim_id_idx" now contains 2274280
> row versions in 85725 pages
> DETAIL: 759323 index row versions were removed.
> 75705 index pages have been deleted, 73721 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_merchant_dim_id_idx" now contains
> 2274280 row versions in 80023 pages
> DETAIL: 759969 index row versions were removed.
> 71588 index pages have been deleted, 69210 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_transaction_date_idx" now contains
> 2274280 row versions in 144196 pages
> DETAIL: 759969 index row versions were removed.
> 126451 index pages have been deleted, 126451 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_transaction_id_idx" now contains
> 2274280
> row versions in 150529 pages
> DETAIL: 759969 index row versions were removed.
> 130649 index pages have been deleted, 130649 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_product_date_idx" now contains 2274280
> row versions in 202248 pages
> DETAIL: 759969 index row versions were removed.
> 174652 index pages have been deleted, 174652 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_merchant_product_date_idx" now
> contains
> 2274280 row versions in 202997 pages
> DETAIL: 759969 index row versions were removed.
> 175398 index pages have been deleted, 175398 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_merchant_date_idx" now contains
> 2274280
> row versions in 203561 pages
> DETAIL: 759969 index row versions were removed.
> 175960 index pages have been deleted, 175960 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_success_idx" now contains 2274280 row
> versions in 78237 pages
> DETAIL: 759969 index row versions were removed.
> 70239 index pages have been deleted, 67652 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_failed_idx" now contains 2274280 row
> versions in 78230 pages
> DETAIL: 759969 index row versions were removed.
> 70231 index pages have been deleted, 67665 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_archived_idx" now contains 2274280 row
> versions in 72943 pages
> DETAIL: 759969 index row versions were removed.
> 64962 index pages have been deleted, 62363 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_response_code_idx" now contains
> 2274280
> row versions in 16918 pages
> DETAIL: 759969 index row versions were removed.
> 8898 index pages have been deleted, 6314 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_transaction_source_idx" now contains
> 2274280 row versions in 14235 pages
> DETAIL: 759969 index row versions were removed.
> 6234 index pages have been deleted, 3663 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "transaction_facts_transaction_id_source_idx" now
> contains
> 2274280 row versions in 18053 pages
> DETAIL: 759969 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "transaction_facts": found 759969 removable, 2274280
> nonremovable
> row versions in 308142 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 15710471 unused item pointers.
> 266986 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 58.00s/35.59u sec elapsed 3240.94 sec.
> INFO: analyzing "public.transaction_facts"
> INFO: "transaction_facts": scanned 15000 of 308142 pages, containing
> 113476 live rows and 0 dead rows; 15000 rows in sample, 2331115
> estimated total rows
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>