Postgres Pro
Downloads
Home   >   mailing lists

Re: contrib/cache_scan (Re: What's needed for cache-only table scan?) - Mailing list pgsql-hackers

From Haribabu Kommi
Subject Re: contrib/cache_scan (Re: What's needed for cache-only table scan?)
Date
Msg-id CAJrrPGedhk0vG9029KEiW=87CMDnOs9R-GWKmeBp-WOmE72DQw@mail.gmail.com
Whole thread Raw
In response to Re: contrib/cache_scan (Re: What's needed for cache-only table scan?)  (Haribabu Kommi <kommi.haribabu@gmail.com>)
List pgsql-hackers
Tree view
On Tue, Feb 25, 2014 at 11:13 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
Thanks for the information, I will apply other patches also and start testing. 

When try to run the pgbench test, by default the cache-scan plan is not chosen because of more cost. So I increased the cpu_index_tuple_cost to a maximum value or by turning off index_scan, so that the plan can chose the cache_scan as the least cost.

The configuration parameters changed during the test are,

shared_buffers - 2GB, cache_scan.num_blocks - 1024
wal_buffers - 16MB, checkpoint_segments - 255
checkpoint_timeout - 15 min, cpu_index_tuple_cost - 100000 or enable_indexscan=off

Test procedure:
1. Initialize the database with pgbench with 75 scale factor.
2. Create the triggers on pgbench_accounts
3. Use a select query to load all the data into cache.
4. Run  a simple update pgbench test.

Plan details of pgbench simple update queries:

postgres=# explain update pgbench_accounts set abalance = abalance where aid = 100000;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Update on pgbench_accounts  (cost=0.43..100008.44 rows=1 width=103)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..100008.44 rows=1 width=103)
         Index Cond: (aid = 100000)
 Planning time: 0.045 ms
(4 rows)

postgres=# explain select abalance from pgbench_accounts where aid = 100000;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Custom Scan (cache scan) on pgbench_accounts  (cost=0.00..99899.99 rows=1 width=4)
   Filter: (aid = 100000)
 Planning time: 0.042 ms
(3 rows)

I am observing a too much delay in performance results. The performance test script is attached in the mail.
please let me know if you find any problem in the test.
 
Regards,
Hari Babu
Fujitsu Australia
Attachment

pgsql-hackers by date:

Previous
From: Kyotaro HORIGUCHI
Date:
Subject: Re: inherit support for foreign tables
Next
From: Kyotaro HORIGUCHI
Date:
Subject: Re: Get more from indices.