From: Richard Huxton <dev@archonet.com>
To: Jeremiah Jahn <jeremiah@cs.earlham.edu>
To: Hannu Krosing <hannu@tm.ee>
Cc: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: cross table indexes or something?
Date: Mon, 1 Dec 2003 15:59:52 +0000
Message-ID: <200312011559.52880.dev@archonet.com> (raw)
In-Reply-To: <1070288942.22346.27.camel@bluejay.goodinassociates.com>
References: <1069877651.22346.13.camel@bluejay.goodinassociates.com>
<1069885949.3176.57.camel@fuji.krosing.net>
<1070288942.22346.27.camel@bluejay.goodinassociates.com>
On Monday 01 December 2003 14:29, Jeremiah Jahn wrote:
> On Wed, 2003-11-26 at 16:32, Hannu Krosing wrote:
> > Jeremiah Jahn kirjutas K, 26.11.2003 kell 22:14:
> > > I was wondering if there is something I can do that would act similar
> > > to a index over more than one table.
> > >
> > > I have about 3 million people in my DB at the moment, they all have
> > > roles, and many of them have more than one name.
> > >
> > > for example, a Judge will only have one name, but a Litigant could have
> > > multiple aliases. Things go far to slow when I do a query on a judge
> > > named smith.
> >
> > If you dont need all the judges named smith you could try to use LIMIT.
>
> Unfortunately I do need all of the judges named smith.
>
> > Have you run ANALYZE ? Why does DB think that there is only one judge
> > with name like SMITH% ?
>
> I've attached the Analyze below. I have no idea why the db thinks there
> is only 1 judge named simth. Is there some what I can inform the DB
> about this. In actuality, there aren't any judges named smith at the
> moment, but there are 22K people named smith.
It's guessing there's approximately 1. I don't think PG measures
cross-correlation of various columns cross-table.
If role_class_code on table actor? If so, try:
CREATE INDEX test_judge_idx ON actor (actor_id) WHERE role_class_code =
'Judge';
And then similar for the other class-codes (assuming you've not got too many
of them). Or even just an index on (actor_id,role_class_code).
If role_class_code is on a different table, can you say which one? The problem
is clearly this step:
> -> Index Scan using actor_speed on
> actor (cost=0.00..5.42 rows=1 width=50) (actual time=4.883..4.883 rows=0
> loops=22436)
> Index Cond: (("outer".actor_id)::text =
> (actor.actor_id)::text) Filter: ((role_class_code)::text = 'Judge'::text)
Thats 4.883 * 22436 loops = 109555 milliseconds.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, jeremiah@cs.earlham.edu, hannu@tm.ee
Subject: Re: cross table indexes or something?
In-Reply-To: <200312011559.52880.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox