Message-ID: <4304CB77.4070403@arbash-meinel.com>
Date: Thu, 18 Aug 2005 12:55:03 -0500
From: John Arbash Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727)
MIME-Version: 1.0
To: Jeremiah Jahn <jeremiah@cs.earlham.edu>
Cc: Jeff Trout <threshar@torgo.978.org>,
	postgres performance <pgsql-performance@postgresql.org>
Subject: Re: extremly low memory usage
References: <1124331108.27881.96.camel@bluejay.goodinassociates.com>	
	<EA4219B9-E2B0-4756-ABF2-D84F245FDFB5@torgo.978.org>
	<1124386761.27881.119.camel@bluejay.goodinassociates.com>
In-Reply-To: <1124386761.27881.119.camel@bluejay.goodinassociates.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigEB715DABC17B44CB3B41A889"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigEB715DABC17B44CB3B41A889
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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


--------------enigEB715DABC17B44CB3B41A889
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDBMt3JdeBCYSNAAMRAj33AJ9qq96tbNdUfwLtoN5YdV8t8mRgmQCgp/rk
mZgsruuYF/yOK8Smgkk+XQE=
=CRGt
-----END PGP SIGNATURE-----

--------------enigEB715DABC17B44CB3B41A889--