Re: PG8.2.1 choosing slow seqscan over idx scan - Mailing list pgsql-performance
From | Dave Cramer |
---|---|
Subject | Re: PG8.2.1 choosing slow seqscan over idx scan |
Date | |
Msg-id | 44BC14E1-2CBF-465A-AD6E-443687BBB98D@fastcrypt.com Whole thread Raw |
In response to | Re: PG8.2.1 choosing slow seqscan over idx scan ("Jeremy Haile" <jhaile@fastmail.fm>) |
Responses |
Re: PG8.2.1 choosing slow seqscan over idx scan
|
List | pgsql-performance |
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 >
pgsql-performance by date: