A *short* planner question

Поиск
Список
Период
Сортировка
От Nick Fankhauser
Тема A *short* planner question
Дата
Msg-id NEBBLAAHGLEEPCGOBHDGMECJEMAA.nickf@ontko.com
обсуждение исходный текст
Ответы Re: A *short* planner question  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
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






В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: ALTER TABLE ... SET DEFAULT
Следующее
От: Tom Lane
Дата:
Сообщение: Re: A *short* planner question