Re: extremly low memory usage - Mailing list pgsql-performance

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
=:->