The system is a dual Xenon with 6Gig of ram and 14 73Gig 15K u320 scsi
drives. Plus 2 raid 1 system dives.

RedHat EL ES4 is the OS. 


Any1 have any suggestions as to the configuration? The database is about
60 Gig's. Should jump to 120 here quite soon. Mus of the searches
involve people's names. Through a website. My current setup just doesn't
seem to have resulted in the performance kick I wanted. I don't know if
it's LVM or what. The strang thing is that My Memory usage stays very
LOW for some reason. While on my current production server it stays very
high. Also looking for ideas on stipe and extent size. The below is run
off of a RAID 10. I have not moved my WAL file yet, but there were no
incoming transactions at the time the query was run. My stats on the
identity table are set to 1000.



> 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 'SMITH%' order by  full_name;
>                                                                               
>                                                                               
>                                                                           
> QUERY PLAN 
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=34042.46..34042.57 rows=3 width=173) (actual 
> time=63696.896..63720.193 rows=8086 loops=1)
>    ->  Sort  (cost=34042.46..34042.47 rows=3 width=173) (actual 
> time=63696.892..63702.239 rows=8086 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..34042.43 rows=3 width=173) (actual 
> time=135.498..63655.542 rows=8086 loops=1)
>                ->  Nested Loop  (cost=0.00..34037.02 rows=1 width=159) 
> (actual time=95.760..34637.611 rows=8086 loops=1)
>                      ->  Nested Loop  (cost=0.00..34033.72 rows=1 width=138) 
> (actual time=89.222..34095.763 rows=8086 loops=1)
>                            Join Filter: (("outer".case_id)::text = 
> ("inner".case_id)::text)
>                            ->  Index Scan using name_speed on identity  
> (cost=0.00..1708.26 rows=8152 width=82) (actual time=42.589..257.818 
> rows=8092 loops=1)
>                                  Index Cond: (((full_name)::text >= 
> 'SMITH'::character varying) AND ((full_name)::text < 'SMITI'::character 
> varying))
>                                  Filter: (((court_ori)::text = 
> 'IL081025J'::text) AND ((full_name)::text ~~ 'SMITH%'::text))
>                            ->  Index Scan using lit_actor_speed on 
> litigant_details  (cost=0.00..3.95 rows=1 width=81) (actual time=4.157..4.170 
> rows=1 loops=8092)
>                                  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.051..0.058 rows=1 loops=8086)
>                            Filter: ('IL081025J'::text = (id)::text)
>                ->  Index Scan using case_data_pkey on case_data  
> (cost=0.00..5.36 rows=2 width=53) (actual time=3.569..3.572 rows=1 loops=8086)
>                      Index Cond: (('IL081025J'::text = 
> (case_data.court_ori)::text) AND ((case_data.case_id)::text = 
> ("outer".case_id)::text))
>  Total runtime: 63727.873 ms
> 
> 



> tcpip_socket = true
> max_connections = 100
> shared_buffers = 50000          # min 16, at least max_connections*2, 8KB each
> sort_mem = 2024000              # min 64, size in KB
> vacuum_mem = 819200             # min 1024, size in KB
> checkpoint_segments = 20        # in logfile segments, min 1, 16MB each
> effective_cache_size = 3600000  # typically 8KB each
> random_page_cost = 2            # units are one sequential page fetch cost
> log_min_duration_statement = 10000 # Log all statements whose
> lc_messages = 'C'               # locale for system error message strings
> lc_monetary = 'C'               # locale for monetary formatting
> lc_numeric = 'C'                # locale for number formatting
> lc_time = 'C'                   # locale for time formatting





Ingrate, n.: A man who bites the hand that feeds him, and then complains
of indigestion.

Attachment: signature.asc
Description: This is a digitally signed message part

Reply via email to