Re: Performance Optimization for Dummies 2 - the SQL

От: Carlo Stonebanks
Тема: Re: Performance Optimization for Dummies 2 - the SQL
Дата: ,
Msg-id: eg2cds$29en$1@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks")
Ответы: Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane)
Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure")
Список: pgsql-performance

Скрыть дерево обсуждения

Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Alex Stapleton, )
   Re: Performance Optimization for Dummies 2 - the SQL  (Markus Schaber, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  index growth problem  (Graham Davis, )
   Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Graham Davis, )
     Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Scott Marlowe, )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Shaun Thomas, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )

> do we have an multi-column index on
> facility_address(facility_id, address_id)?  did you run analyze?

There is an index on facility_address on facility_id.

I didn't create an index on facility_address.address_id because I expected
joins to go in the other direction (from facility_address to address).
Nor did I create a multi-column index on facility_id, address_id because I
had yet to come up with a query that required that.

However, I still have a lot to learn about how SQL chooses its indexes, how
multi-column indexes are used, and when to use them (other than the
obvious - i.e. sort orders or relational expressions which request those
columns in one search expression)

Analyse is actually run every time a page of imported data loads into the
client program. This is currently set at 500 rows.

Carlo

>> explain analyze select
>>       f.facility_id,
>>       fa.facility_address_id,
>>       a.address_id,
>>       f.facility_type_code,
>>       f.name,
>>       a.address,
>>       a.city,
>>       a.state_code,
>>       a.postal_code,
>>       a.country_code
>>    from
>>       mdx_core.facility as f
>>    join mdx_core.facility_address as fa
>>       on fa.facility_id = f.facility_id
>>    join mdx_core.address as a
>>       on a.address_id = fa.address_id
>>    where
>>       (a.country_code, a.state_code, mdx_core.zip_trunc(a.postal_code)) =
>> ('US', 'IL', mdx_core.zip_trunc('60640-5759'))
>>    order by facility_id
>>
>> "Sort  (cost=6474.78..6474.84 rows=25 width=103) (actual
>> time=217.279..217.311 rows=65 loops=1)"
>> "  Sort Key: f.facility_id"
>> "  ->  Nested Loop  (cost=2728.54..6474.20 rows=25 width=103) (actual
>> time=35.828..217.059 rows=65 loops=1)"
>> "        ->  Hash Join  (cost=2728.54..6384.81 rows=25 width=72) (actual
>> time=35.801..216.117 rows=65 loops=1)"
>> "              Hash Cond: ("outer".address_id = "inner".address_id)"
>> "              ->  Seq Scan on facility_address fa  (cost=0.00..3014.68
>> rows=128268 width=12) (actual time=0.007..99.072 rows=128268 loops=1)"
>> "              ->  Hash  (cost=2728.50..2728.50 rows=19 width=64) (actual
>> time=33.618..33.618 rows=39 loops=1)"
>> "                    ->  Bitmap Heap Scan on address a
>> (cost=48.07..2728.50
>> rows=19 width=64) (actual time=2.569..33.491 rows=39 loops=1)"
>> "                          Recheck Cond: ((country_code = 'US'::bpchar)
>> AND
>> ((state_code)::text = 'IL'::text))"
>> "                          Filter: (mdx_core.zip_trunc(postal_code) =
>> '60640'::text)"
>> "                          ->  Bitmap Index Scan on
>> address_country_state_zip_trunc_idx  (cost=0.00..48.07 rows=3846 width=0)
>> (actual time=1.783..1.783 rows=3554 loops=1)"
>> "                                Index Cond: ((country_code =
>> 'US'::bpchar)
>> AND ((state_code)::text = 'IL'::text))"
>> "        ->  Index Scan using facility_pkey on facility f
>> (cost=0.00..3.56
>> rows=1 width=35) (actual time=0.009..0.010 rows=1 loops=65)"
>> "              Index Cond: ("outer".facility_id = f.facility_id)"
>> "Total runtime: 217.520 ms"




В списке pgsql-performance по дате сообщения:

От: "Merlin Moncure"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL
От: "Carlo Stonebanks"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL