Skip site navigation (1) Skip section navigation (2)

Re: extremly low memory usage

From: Jeremiah Jahn <jeremiah(at)cs(dot)earlham(dot)edu>
To: Jeff Trout <threshar(at)torgo(dot)978(dot)org>
Cc: postgres performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: extremly low memory usage
Date: 2005-08-18 17:39:21
Message-ID: 1124386761.27881.119.camel@bluejay.goodinassociates.com (view raw or flat)
Thread:
Lists: pgsql-performance
here's an example standard query. Ireally have to make the first hit go
faster. The table is clustered as well on full_name as well. 'Smith%'
took 87 seconds on the first hit. I wonder if I set up may array wrong.
I remeber see something about DMA access versus something else, and
choose DMA access. LVM maybe?

explain analyze select distinct case_category,identity_id,court.name,litigant_details.case_id,case_year,date_of_birth,assigned_case_role,litigant_details.court_ori,full_name,litigant_details.actor_id,case_data.type_code,case_data.subtype_code,litigant_details.impound_litigant_data, to_number(trim(leading case_data.type_code from trim(leading case_data.case_year from case_data.case_id)),'999999') as seq from identity,court,litigant_details,case_data where identity.court_ori = litigant_details.court_ori and identity.case_id = litigant_details.case_id and identity.actor_id = litigant_details.actor_id and court.id = identity.court_ori and identity.court_ori = case_data.court_ori and case_data.case_id = identity.case_id  and identity.court_ori = 'IL081025J' and full_name like 'MILLER%' order by  full_name;
                                                                                                                                                                                                                                      QUERY PLAN 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=20411.84..20411.91 rows=2 width=173) (actual time=38340.231..38355.120 rows=4906 loops=1)
   ->  Sort  (cost=20411.84..20411.84 rows=2 width=173) (actual time=38340.227..38343.667 rows=4906 loops=1)
         Sort Key: identity.full_name, case_data.case_category, identity.identity_id, court.name, litigant_details.case_id, case_data.case_year, identity.date_of_birth, litigant_details.assigned_case_role, litigant_details.court_ori, litigant_details.actor_id, case_data.type_code, case_data.subtype_code, litigant_details.impound_litigant_data, to_number(ltrim(ltrim((case_data.case_id)::text, (case_data.case_year)::text), (case_data.type_code)::text), '999999'::text)
         ->  Nested Loop  (cost=0.00..20411.83 rows=2 width=173) (actual time=12.891..38317.017 rows=4906 loops=1)
               ->  Nested Loop  (cost=0.00..20406.48 rows=1 width=159) (actual time=12.826..23232.106 rows=4906 loops=1)
                     ->  Nested Loop  (cost=0.00..20403.18 rows=1 width=138) (actual time=12.751..22885.439 rows=4906 loops=1)
                           Join Filter: (("outer".case_id)::text = ("inner".case_id)::text)
                           ->  Index Scan using name_speed on identity  (cost=0.00..1042.34 rows=4868 width=82) (actual time=0.142..52.538 rows=4915 loops=1)
                                 Index Cond: (((full_name)::text >= 'MILLER'::character varying) AND ((full_name)::text < 'MILLES'::character varying))
                                 Filter: (((court_ori)::text = 'IL081025J'::text) AND ((full_name)::text ~~ 'MILLER%'::text))
                           ->  Index Scan using lit_actor_speed on litigant_details  (cost=0.00..3.96 rows=1 width=81) (actual time=4.631..4.635 rows=1 loops=4915)
                                 Index Cond: (("outer".actor_id)::text = (litigant_details.actor_id)::text)
                                 Filter: ('IL081025J'::text = (court_ori)::text)
                     ->  Seq Scan on court  (cost=0.00..3.29 rows=1 width=33) (actual time=0.053..0.062 rows=1 loops=4906)
                           Filter: ('IL081025J'::text = (id)::text)
               ->  Index Scan using case_speed on case_data  (cost=0.00..5.29 rows=3 width=53) (actual time=3.049..3.058 rows=1 loops=4906)
                     Index Cond: (('IL081025J'::text = (case_data.court_ori)::text) AND ((case_data.case_id)::text = ("outer".case_id)::text))
 Total runtime: 38359.722 ms
(18 rows)

copa=> explain analyze select distinct case_category,identity_id,court.name,litigant_details.case_id,case_year,date_of_birth,assigned_case_role,litigant_details.court_ori,full_name,litigant_details.actor_id,case_data.type_code,case_data.subtype_code,litigant_details.impound_litigant_data, to_number(trim(leading case_data.type_code from trim(leading case_data.case_year from case_data.case_id)),'999999') as seq from identity,court,litigant_details,case_data where identity.court_ori = litigant_details.court_ori and identity.case_id = litigant_details.case_id and identity.actor_id = litigant_details.actor_id and court.id = identity.court_ori and identity.court_ori = case_data.court_ori and case_data.case_id = identity.case_id  and identity.court_ori = 'IL081025J' and full_name like 'MILLER%' order by  full_name;
                                                                                                                                                                                                                                      QUERY PLAN 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=20411.84..20411.91 rows=2 width=173) (actual time=666.832..688.081 rows=4906 loops=1)
   ->  Sort  (cost=20411.84..20411.84 rows=2 width=173) (actual time=666.825..671.833 rows=4906 loops=1)
         Sort Key: identity.full_name, case_data.case_category, identity.identity_id, court.name, litigant_details.case_id, case_data.case_year, identity.date_of_birth, litigant_details.assigned_case_role, litigant_details.court_ori, litigant_details.actor_id, case_data.type_code, case_data.subtype_code, litigant_details.impound_litigant_data, to_number(ltrim(ltrim((case_data.case_id)::text, (case_data.case_year)::text), (case_data.type_code)::text), '999999'::text)
         ->  Nested Loop  (cost=0.00..20411.83 rows=2 width=173) (actual time=0.216..641.366 rows=4906 loops=1)
               ->  Nested Loop  (cost=0.00..20406.48 rows=1 width=159) (actual time=0.149..477.063 rows=4906 loops=1)
                     ->  Nested Loop  (cost=0.00..20403.18 rows=1 width=138) (actual time=0.084..161.045 rows=4906 loops=1)
                           Join Filter: (("outer".case_id)::text = ("inner".case_id)::text)
                           ->  Index Scan using name_speed on identity  (cost=0.00..1042.34 rows=4868 width=82) (actual time=0.047..37.898 rows=4915 loops=1)
                                 Index Cond: (((full_name)::text >= 'MILLER'::character varying) AND ((full_name)::text < 'MILLES'::character varying))
                                 Filter: (((court_ori)::text = 'IL081025J'::text) AND ((full_name)::text ~~ 'MILLER%'::text))
                           ->  Index Scan using lit_actor_speed on litigant_details  (cost=0.00..3.96 rows=1 width=81) (actual time=0.015..0.017 rows=1 loops=4915)
                                 Index Cond: (("outer".actor_id)::text = (litigant_details.actor_id)::text)
                                 Filter: ('IL081025J'::text = (court_ori)::text)
                     ->  Seq Scan on court  (cost=0.00..3.29 rows=1 width=33) (actual time=0.049..0.056 rows=1 loops=4906)
                           Filter: ('IL081025J'::text = (id)::text)
               ->  Index Scan using case_speed on case_data  (cost=0.00..5.29 rows=3 width=53) (actual time=0.017..0.020 rows=1 loops=4906)
                     Index Cond: (('IL081025J'::text = (case_data.court_ori)::text) AND ((case_data.case_id)::text = ("outer".case_id)::text))
 Total runtime: 694.639 ms
(18 rows)



On Thu, 2005-08-18 at 09:00 -0400, Jeff Trout wrote:
> On Aug 17, 2005, at 10:11 PM, Jeremiah Jahn wrote:
> 
> > I just put together a system with 6GB of ram on a 14 disk raid 10  
> > array.
> > When I run my usual big painful queries, I get very little to know
> > memory usage. My production box (raid 5 4GB ram) hovers at 3.9GB used
> > most of the time. the new devel box sits at around 250MB.
> >
> 
> Is the system performing fine?  Are you touching as much data as the  
> production box?
> 
> If the system is performing fine don't worry about it.
> 
> > work_mem = 2097151              # min 64, size in KB
> 
> This is EXTREMELY high.  You realize this is the amount of memory  
> that can be used per-sort and per-hash build in a query? You can end  
> up with multiples of this on a single query.   If you have some big  
> queries that are run infrequently have them set it manually.
> 
> > effective_cache_size = 3600000   <-----this is a little out of  
> > control, but would it have any real effect?
> 
> This doesn't allocate anything - it is a hint to the planner about  
> how much data it can assume is cached.
> 
> --
> Jeff Trout <jeff(at)jefftrout(dot)com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
-- 
"Now this is a totally brain damaged algorithm.  Gag me with a
smurfette."
                -- P. Buhr, Computer Science 354


In response to

Responses

pgsql-performance by date

Next:From: John Arbash MeinelDate: 2005-08-18 17:55:03
Subject: Re: extremly low memory usage
Previous:From: John Arbash MeinelDate: 2005-08-18 17:14:01
Subject: Re: extremly low memory usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group