Re: Massive performance issues

Поиск
Список
Период
Сортировка
От Mark Kirkwood
Тема Re: Massive performance issues
Дата
Msg-id 4317C4D9.9070106@paradise.net.nz
обсуждение исходный текст
Ответ на Massive performance issues  (Matthew Sackman <matthew@lshift.net>)
Список pgsql-performance
It would be good to see EXPLAIN ANALYZE output for the three queries
below (the real vs. estimated row counts being of interest).

The number of pages in your address table might be interesting to know too.

regards

Mark

Matthew Sackman wrote (with a fair bit of snippage):
> explain select locality_2 from address where locality_2 = 'Manchester';
> gives
>                            QUERY PLAN
> ----------------------------------------------------------------
>  Seq Scan on address  (cost=0.00..80677.16 rows=27923 width=12)
>    Filter: ((locality_2)::text = 'Manchester'::text)
>
>
> explain select locality_1 from address where locality_1 = 'Manchester';
> gives
>                            QUERY PLAN
> ----------------------------------------------------------------
>  Index Scan using address_locality_1_index on address
> (cost=0.00..69882.18 rows=17708 width=13)
>    Index Cond: ((locality_1)::text = 'Manchester'::text)
>
 >
> select street, locality_1, locality_2, city from address
> where (city = 'Nottingham' or locality_2 = 'Nottingham'
>        or locality_1 = 'Nottingham')
>   and upper(substring(street from 1 for 1)) = 'A'
> group by street, locality_1, locality_2, city
> order by street
> limit 20 offset 0
>

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

Предыдущее
От: Ricardo Humphreys
Дата:
Сообщение: Avoid using swap in a cluster
Следующее
От: Hemant Pandey
Дата:
Сообщение: Update is more affected( taking more time) than Select ( if Auto vacuum is not running)