Re: PG8.2.1 choosing slow seqscan over idx scan

> 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.

> 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? =)

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