Re: extremly low memory usage

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