Re: extremly low memory usage - Mailing list 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(leadingcase_data.type_code from trim(leading case_data.case_year from case_data.case_id)),'999999') as
seqfrom 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_oriand 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.538rows=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)
(actualtime=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.058rows=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(leadingcase_data.type_code from trim(leading case_data.case_year from case_data.case_id)),'999999') as
seqfrom 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_oriand 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.898rows=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)
(actualtime=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.020rows=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@jefftrout.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