Re: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

Hi Kevin,

I got one more question, please help me out.

Question 1. How do we correlate our memory with kernel parameters, I mean to say is there any connection between shared_buffer and kernel SHMMAX. For example if I define my shared buffer more than my current SHMMAX value, it would not allow me to use that ??or vice versa. Please throw some light.

Questions 2. I want to show the last result of last query before and after changing the parameters, I found performance was degraded.

USED  EXPLAIN ANALYZE

radius=# explain analyze select * from subsexpired where subsno between 5911 and 50911 and subsno not in (select subsno from subs where subsno between  5911 and 50911);
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_sub_new on subsexpired  (cost=1943.39..6943.84 rows=30743 width=69) (actual time=124.628..142.203 rows=430 loops=1)
   Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 62079
   SubPlan 1
     ->  Index Only Scan using subs_pkey on subs  (cost=0.00..1876.77 rows=26647 width=4) (actual time=0.030..44.743 rows=27397 loops=1)
           Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
           Heap Fetches: 27397
 Total runtime: 142.812 ms
----------------------------------------------------------------------------------------------------------------------

After: using the parameters as suggested.

radius=# explain analyze select * from subsexpired where subsno between 5911 and 50911 and subsno not in (select subsno from subs where subsno between  5911 and 50911);
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_sub_new on subsexpired  (cost=1943.39..6943.84 rows=30743 width=69) (actual time=128.351..144.532 rows=430 loops=1)
   Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 62079
   SubPlan 1
     ->  Index Only Scan using subs_pkey on subs  (cost=0.00..1876.77 rows=26647 width=4) (actual time=0.030..47.848 rows=27397 loops=1)
           Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
           Heap Fetches: 27397
 Total runtime: 145.127 ms
(9 rows)


Thanks