From: Dave Cramer <pg@fastcrypt.com>
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: Wed, 17 Jan 2007 10:14:47 -0500
Message-ID: <44BC14E1-2CBF-465A-AD6E-443687BBB98D@fastcrypt.com> (raw)
In-Reply-To: <1169044649.19974.1169715743@webmail.messagingengine.com>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
<26412.1168983547@sss.pgh.pa.us>
<1168986053.16393.1169598917@webmail.messagingengine.com>
<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
<1169002739.19758.1169636285@webmail.messagingengine.com>
<1169012767.6271.7.camel@localhost.localdomain>
<1169044649.19974.1169715743@webmail.messagingengine.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: pg@fastcrypt.com, jhaile@fastmail.fm
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
In-Reply-To: <44BC14E1-2CBF-465A-AD6E-443687BBB98D@fastcrypt.com>
References: <1168982580.8275.1169588211@webmail.messagingengine.com> <26412.1168983547@sss.pgh.pa.us> <1168986053.16393.1169598917@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> <1169002739.19758.1169636285@webmail.messagingengine.com> <1169012767.6271.7.camel@localhost.localdomain> <1169044649.19974.1169715743@webmail.messagingengine.com> <44BC14E1-2CBF-465A-AD6E-443687BBB98D@fastcrypt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox