Skip site navigation (1) Skip section navigation (2)

Re: A *short* planner question

From: "Nick Fankhauser" <nickf(at)ontko(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: A *short* planner question
Date: 2002-04-12 23:31:48
Message-ID: NEBBLAAHGLEEPCGOBHDGOECNEMAA.nickf@ontko.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
> You could
> check by temporarily dropping the actor_case_assignment_both index and
> seeing what plan you get.

Here is the result:

Index Scan using actor_upper_full_name on actor  (cost=0.00..1544484.16
rows=3051 width=40)
  SubPlan
    ->  Nested Loop  (cost=0.00..21275.72 rows=42 width=24)
          ->  Index Scan using actor_case_assignment_fk1 on
actor_case_assignment  (cost=0.00..9221.62 rows=2696 width=12)
          ->  Index Scan using case_data_case_id on case_data
(cost=0.00..4.46 rows=1 width=12)

Lightning-fast, but I need that index on both ids for other purposes.

The problem is that I need the index on both foreign keys because I use it
to kick out duplicate entry attempts during my load process. (Duplicate
actors are ok, and duplicate cases are ok, but an actor can only be assigned
to a case once, so the combination must be unique.)

Fortunately, your info on the function index not using stats got me thinking
in a profitable direction:

I'm using the "exists" subquery only because when I used "distinct", I got
even worse performance. I think this was because the planner chose not to
use my index on upper(actor_full_name) when I simply joined all of the
tables. If I reword the query to use "distinct", and eliminate also the
upper() on my constraint, the query really flies.

So I think my solution will be to add a new column called
"upper_actor_full_name" to my "actor" table, and add a bit of code to my
load process that will populate this field with upper(actor_full_name). It's
a bit of a kludge, but should work until the day that you get to adding
stats for function indexes.

Thanks for the help.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf(at)ontko(dot)com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


In response to

Responses

pgsql-admin by date

Next:From: Brian McCaneDate: 2002-04-13 00:19:01
Subject: Re: ALTER TABLE ... SET DEFAULT
Previous:From: Gianmarco PiolaDate: 2002-04-12 23:29:22
Subject: upgrade

pgsql-general by date

Next:From: Johann ZuschlagDate: 2002-04-12 23:56:52
Subject: SI buffer overflow, cache state reset
Previous:From: Tom LaneDate: 2002-04-12 22:29:46
Subject: Re: A *short* planner question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group