Jeremiah Jahn wrote: >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? > > It would be nice if you would format your queries to be a little bit easier to read before posting them. However, I believe I am reading it correctly, to say that the index scan on identity is not your slow point. In fact, as near as I can tell, it only takes 52ms to complete.
The expensive parts are the 4915 lookups into the litigant_details (each one takes approx 4ms for a total of ~20s). And then you do it again on case_data (average 3ms each * 4906 loops = ~15s). So there is no need for preloading your indexes on the identity table. It is definitely not the bottleneck. So a few design bits, which may help your database. Why is "actor_id" a text field instead of a number? You could try creating an index on "litigant_details (actor_id, count_ori)" so that it can do just an index lookup, rather than an index + filter. More importantly, though, the planner seems to think the join of identity to litigant_details will only return 1 row, not 5000. Do you regularly vacuum analyze your tables? Just as a test, try running: set enable_nested_loop to off; And then run EXPLAIN ANALYZE again, just to see if it is faster. You probably need to increase some statistics targets, so that the planner can design better plans. > -> 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)) John =:->
Description: OpenPGP digital signature