A *short* planner question

From: "Nick Fankhauser" <nickf(at)ontko(dot)com>
To: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Subject: A *short* planner question
Date: 2002-04-12 21:56:03
Message-ID: NEBBLAAHGLEEPCGOBHDGMECJEMAA.nickf@ontko.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

I know I'm about to become a pest, but I promise, this is a short one!

Before doing the explain below, I specifically did a verbose analyze & noted
that the results seemed in line with what I expected. I'm on v7.1.3 of PGSQL

Here's the query that runs too slow: (It takes about 30 seconds on a 1.2Ghz
Athlon system with 512MB RAM)

monroe=# explain
monroe-# select
monroe-# actor.actor_id,
monroe-# actor.role_class_code,
monroe-# actor.actor_full_name,
monroe-# actor.actor_person_date_of_birth
monroe-# from actor
monroe-# where exists (select 'x'
monroe(# from actor_case_assignment,case_data
monroe(# where actor_case_assignment.actor_id = actor.actor_id
monroe(# and actor_case_assignment.case_id =
case_data.case_id
monroe(# and case_data.case_disp_global_code = 'Open')
monroe-# and upper(actor_full_name) like 'RAY, J%';

NOTICE: QUERY PLAN:

Index Scan using actor_upper_full_name on actor (cost=0.00..1284478.54
rows=2799 width=40)
SubPlan
-> Nested Loop (cost=0.00..19291.12 rows=42 width=24)
-> Seq Scan on case_data (cost=0.00..5424.69 rows=2871 width=12)
-> Index Scan using actor_case_assignment_both on
actor_case_assignment (cost=0.00..4.82 rows=1 width=12)

The index actor_case_assignment_both is on (case_id, actor_id) in that
order.
There is also an index on actor_case_assigment(actor_id).
There is a unique index on case_data(case_id).
The is an index on upper(actor_full_name).
case_data.case_disp_global_code has only two distinct values ('Open',
'Closed').
actor contains 279855 rows.
case_data contains 168775 rows.
actor_case_assignment contains 753610 rows.

So... why wouldn't the planner do this:

Index scan on actor for upper(actor_full_name) like 'RAY, J%' returns a
small set (rows < 1000)
For each actor_id in the set:
do an Index Scan using actor_case_assignment.actor_id to return
actor_case_assignment.case_id (rows < 2000)
For each case_id:
do an index scan to return case_data.case_disp_global_code
Scan each code for 'Open' (rows < 100)

Any thoughts on how I might encourage the planner to use such a strategy?

Another thing that doesn't make sense to me, but doesn't hurt the plan, is
that although we have analyze stats on actor, the planner seems to be using
the default of .01 on upper(actor_full_name) and predicts that 2799 rows
will be returned, however, if I get rid of the upper(), it uses the stats
and predicts that 1 row will be returned. Does the use of an index on a
function make the planner stop using the stats?

Thanks,

-Nick

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2002-04-12 22:29:46 Re: A *short* planner question
Previous Message Tom Lane 2002-04-12 14:00:46 Re: ALTER TABLE ... SET DEFAULT

Browse pgsql-general by date

  From Date Subject
Next Message Ian Harding 2002-04-12 21:57:22 Re: Nested Loop WAS: VACUUM ANALYZE makes things
Previous Message Tom Lane 2002-04-12 21:12:47 Re: Nested Loop WAS: VACUUM ANALYZE makes things worse!