Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неоптимальное использование WHERE в VIEW / 19 сообщений из 19, страница 1 из 1
27.08.2008, 13:38
    #35508457
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Добрый день. Не могу понять, есть VIEW:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE OR REPLACE VIEW view_name AS
SELECT "SampleID", min("DateTimeCatched") as "min", "SigID",
"DateTimeAdded", "DateTimeAdded" - "DateTimeCatched" as "InternalDuration"
FROM "sigs"
                JOIN "samples" USING ("SigID")
                JOIN "radar" USING ("SampleID")
                WHERE "DateTimeAdded" >= "DateTimeCatched"
                GROUP BY "SampleID", "DateTimeCatched", "SigID", "DateTimeAdded";

Запрос вида:
Код: plaintext
select * from view_stats_sigs_creation_duration WHERE "min" BETWEEN '2008-08-07' AND '2008-08-08';

выполняется больше минуты.

Если перенести WHERE в сам запрос, т.е.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT "SampleID", min("DateTimeCatched") as min, "SigID",
"DateTimeAdded", "DateTimeAdded" - "DateTimeCatched" as "InternalDuration"
FROM "sigs"
                JOIN "samples" USING ("SigID")
                JOIN "radar" USING ("SampleID")
                WHERE "DateTimeAdded" >= "DateTimeCatched" AND
                "DateTimeCatched" BETWEEN '2008-08-07' AND '2008-08-08'
                GROUP BY "SampleID", "DateTimeCatched", "SigID", "DateTimeAdded";

и соответственно оформить все это в виде функции, то работает меньше двух секунд.

Не могу понять, почему движок обрабатывает по разному WHERE в самом SELECT-е, и внешний WHERE. Что можно сделать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 15:06
    #35508779
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
где explain analyze проблемного запроса ? где версия сервера ?


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 15:14
    #35508805
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
PostgreSQL 8.2.6

explain analyze select * from view_name WHERE "min" BETWEEN '2008-08-07' AND '2008-08-08';

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
"Subquery Scan view_name  (cost=1022371.65..1048787.05 rows=480280 width=40) (actual time=56313.881..56325.077 rows=52 loops=1)"
"  ->  GroupAggregate  (cost=1022371.65..1043984.25 rows=480280 width=24) (actual time=56313.874..56324.858 rows=52 loops=1)"
"        Filter: ((min("DateTimeCatched") >= '2008-08-07 00:00:00'::timestamp without time zone) AND (min("DateTimeCatched") <= '2008-08-08 00:00:00'::timestamp without time zone))"
"        ->  Sort  (cost=1022371.65..1023572.35 rows=480280 width=24) (actual time=56029.280..56159.606 rows=61350 loops=1)"
"              Sort Key: samples."SampleID", radar."DateTimeCatched", sigs."SigID", sigs."DateTimeAdded""
"              ->  Hash Join  (cost=724782.65..966377.28 rows=480280 width=24) (actual time=36109.713..55842.437 rows=61350 loops=1)"
"                    Hash Cond: (samples."SigID" = sigs."SigID")"
"                    Join Filter: (sigs."DateTimeAdded" >= radar."DateTimeCatched")"
"                    ->  Hash Join  (cost=71916.80..161615.02 rows=1440839 width=16) (actual time=6098.566..21026.526 rows=1351756 loops=1)"
"                          Hash Cond: (radar."SampleID" = samples."SampleID")"
"                          ->  Seq Scan on radar  (cost=0.00..37050.39 rows=1440839 width=12) (actual time=0.014..7170.287 rows=1351756 loops=1)"
"                          ->  Hash  (cost=41323.91..41323.91 rows=1810791 width=8) (actual time=6098.220..6098.220 rows=1226187 loops=1)"
"                                ->  Seq Scan on samples  (cost=0.00..41323.91 rows=1810791 width=8) (actual time=0.010..3074.335 rows=1226187 loops=1)"
"                    ->  Hash  (cost=353157.38..353157.38 rows=17241638 width=12) (actual time=29974.566..29974.566 rows=432888 loops=1)"
"                          ->  Seq Scan on sigs  (cost=0.00..353157.38 rows=17241638 width=12) (actual time=0.030..28841.476 rows=432888 loops=1)"
"Total runtime: 56326.491 ms"

Уносим where внутрь view и вызываем:

explain analyze select * from view_name;

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
"Subquery Scan view_name  (cost=78810.84..78814.26 rows=137 width=40) (actual time=7064.790..7065.659 rows=125 loops=1)"
"  ->  HashAggregate  (cost=78810.84..78812.89 rows=137 width=24) (actual time=7064.785..7065.132 rows=125 loops=1)"
"        ->  Nested Loop  (cost=17280.16..78809.12 rows=137 width=24) (actual time=1448.317..7064.215 rows=125 loops=1)"
"              Join Filter: (sigs."DateTimeAdded" >= radar."DateTimeCatched")"
"              ->  Hash Join  (cost=17280.16..74456.11 rows=516 width=16) (actual time=1404.546..5412.471 rows=108 loops=1)"
"                    Hash Cond: (samples."SigID" = sigs."SigID")"
"                    ->  Seq Scan on samples  (cost=0.00..41325.69 rows=1810869 width=8) (actual time=0.011..2686.411 rows=1226200 loops=1)"
"                    ->  Hash  (cost=17218.71..17218.71 rows=4916 width=12) (actual time=4.967..4.967 rows=101 loops=1)"
"                          ->  Bitmap Heap Scan on sigs  (cost=125.15..17218.71 rows=4916 width=12) (actual time=4.400..4.713 rows=101 loops=1)"
"                                Recheck Cond: (("DateTimeAdded" >= '2008-08-07 00:00:00'::timestamp without time zone) AND ("DateTimeAdded" <= '2008-08-08 00:00:00'::timestamp without time zone))"
"                                ->  Bitmap Index Scan on "IDX_DateTimeAdded"  (cost=0.00..123.92 rows=4916 width=0) (actual time=1.274..1.274 rows=4114 loops=1)"
"                                      Index Cond: (("DateTimeAdded" >= '2008-08-07 00:00:00'::timestamp without time zone) AND ("DateTimeAdded" <= '2008-08-08 00:00:00'::timestamp without time zone))"
"              ->  Index Scan using "IDX_radar" on radar  (cost=0.00..8.42 rows=1 width=12) (actual time=13.907..15.282 rows=1 loops=108)"
"                    Index Cond: (samples."SampleID" = radar."SampleID")"
"Total runtime: 7066.025 ms"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 16:00
    #35508994
chAlx
chAlx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyan:

Заменяешь HAVING на WHERE -- не только план, но и результат будет другой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 16:02
    #35509003
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
а где вы там нашли HAVING
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 16:22
    #35509076
chAlx
chAlx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyanа где вы там нашли HAVING
Ну а что такое "min"? Агрегированное значение. Если запрос строить без вьюхи, то его без HAVING не проверишь. А в "оптимизированном" запросе проверяется не "min", а другое поле, которое не имеет отношение к проверке "min".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 16:46
    #35509163
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
не пойму, что и где менять то?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 17:15
    #35509276
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyanне пойму, что и где менять то?подумайте, в первом варианте Вы сравниваете дату с результатом выполнения функции, а во втором варианте - сравниваете дату со значением поля. это по определению разные условия и как следствие - разный результат запроса. если конечно функция не возвращает сами входные значения, не меняя их, в качестве своего результата выполнения.

ps: min здесь бессмыслен, Вы группируете по полю DateTimeCatched и по нему же ищете min. результатом min тогда будет само значение поля DateTimeCatched
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 17:46
    #35509364
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Ёшps: min здесь бессмыслен, Вы группируете по полю DateTimeCatched и по нему же ищете min. результатом min тогда будет само значение поля DateTimeCatched+1

GROUP BY в этом случае получается эквивалентным DISTINCT. а может и DISTINCT не нужен? если например комбинация ("SampleID", "DateTimeCatched", "SigID", "DateTimeAdded") уникальна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2008, 18:16
    #35509432
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
ааа.. да, там ошибка, в group by этого поля не должно быть.
Но все равно непонятно, почему планировщик не видит, что результат выполнения функции (min) это и есть значение поля таблицы и не исполняет как будто where задан внутри.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 00:11
    #35509845
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyan wrote:
> CREATE OR REPLACE VIEW view_name AS
> SELECT "SampleID", min("DateTimeCatched") as "min", "SigID",
> "DateTimeAdded", "DateTimeAdded" - "DateTimeCatched" as "InternalDuration"
> FROM "sigs"
> JOIN "samples" USING ("SigID")
> JOIN "radar" USING ("SampleID")
> WHERE "DateTimeAdded" >= "DateTimeCatched"
> GROUP BY "SampleID", "DateTimeCatched", "SigID", "DateTimeAdded";

У вас очень странный агрегирующий запрос.
Поле "DateTimeCatched" у вас и агрегируется, и не агрегируется.
(в GROUP BY находится). Вы уж там как-то определитесь.
Либо агрегируете его, либо - нет.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 11:06
    #35510306
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
С "DateTimeCatched" понятно, ошибка вышла. VIEW выглядит так:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE OR REPLACE VIEW view_name AS
SELECT "SampleID", min("DateTimeCatched") as "min", "SigID",
"DateTimeAdded", "DateTimeAdded" - "DateTimeCatched" as "InternalDuration"
FROM "sigs"
                JOIN "samples" USING ("SigID")
                JOIN "radar" USING ("SampleID")
                WHERE "DateTimeAdded" >= "DateTimeCatched"
                GROUP BY "SampleID", "SigID", "DateTimeAdded";

Вопрос был не в том где ошибка, а почему view ... where работает намного медленнее, чем where внутри view, хотя используется один и тот же источник - значение колонки в таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 11:45
    #35510419
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyan wrote:

> CREATE OR REPLACE VIEW view_name AS
> SELECT "SampleID", min("DateTimeCatched") as "min", "SigID",
> "DateTimeAdded", "DateTimeAdded" - "DateTimeCatched" as "InternalDuration"
> FROM "sigs"
> JOIN "samples" USING ("SigID")
> JOIN "radar" USING ("SampleID")
> WHERE "DateTimeAdded" >= "DateTimeCatched"
> GROUP BY "SampleID", "SigID", "DateTimeAdded";

На самом деле ничего не изменилось, запрос правильнее не стал.
у вас всё еще есть это поле "DateTimeCatched"
в выражении

"DateTimeAdded" - "DateTimeCatched" as "InternalDuration"

Т.е. и в агрегате, и вне агрегата, но вы теперь еще по нему и
не группируете, что уже совсем плохо. Я не знаю как в PG,
но в некоторых серверах такие запросы вообще
не работают, вызывают семантическую ошибку.
Так что уж определяйтесь, либо вы агрегируете это поле, либо
нет. Третьего не дано.

> Вопрос был не в том где ошибка, а почему view ... where работает намного
> медленнее, чем where внутри view, хотя используется один и тот же
> источник - значение колонки в таблице.

Сначала надо иметь правильный запрос, а потом уже - быстро работающий.
Быстро работающий неправильный запрос никому не нужен.
К тому же неправильный запрос очень большой шанс имеет быть также и небыстрым.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 11:52
    #35510445
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert Ayrapetyanааа.. да, там ошибка, в group by этого поля не должно быть.
Но все равно непонятно, почему планировщик не видит, что результат выполнения функции (min) это и есть значение поля таблицы и не исполняет как будто where задан внутри.потому что в общем случае результат функции можно узнать только выполнив запрос. конкретно с min - об этом можно догадаться но такую оптимизацию никто не делал потому что так min никто не использует. imho :)

если Вы сделаете фильтр по колонке, а не агрегатной функции - то Вы увидите как это условие попадёт внутрь view в его where - как вы и хотели.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 12:01
    #35510473
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
MasterZivЯ не знаю как в PG,
но в некоторых серверах такие запросы вообще
не работают, вызывают семантическую ошибку.
Так что уж определяйтесь, либо вы агрегируете это поле, либо
нет. Третьего не дано.угу, они не должны работать. по стандарту SQL, если я не ошибаюсь.

PG на такой запрос скажет:
Код: plaintext
ERROR:  колонка "DateTimeCatched" должна фигурировать в выражении GROUP BY или использоваться в агрегатной функции
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 13:48
    #35510837
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
На самом деле сейчас запрос выглядит вот так (в виде функции):

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
SELECT "A"."SigID", "A"."SigDateTimeAdded" - "A"."SampleDateTimeCatchedFirst" as "InternalSigCreationDuration"
    FROM (
        SELECT min("radar"."DateTimeCatched") as "SampleDateTimeCatchedFirst", "SigID", "sigs"."DateTimeAdded" as "SigDateTimeAdded"
        FROM "sigs"
            NATURAL JOIN "samples"
            NATURAL JOIN "radar"
            GROUP BY "SigID", "SigDateTimeAdded") "A"
    WHERE "SigDateTimeAdded" BETWEEN DateTimeFrom AND DateTimeTo AND
          "SigDateTimeAdded" >= "SampleDateTimeCatchedFirst";

И работает быстро. Но как, еще раз повторюсь, вытащить
Код: plaintext
WHERE "SigDateTimeAdded" BETWEEN DateTimeFrom AND DateTimeTo
наружу?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2008, 14:58
    #35511119
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Robert AyrapetyanНа самом деле сейчас запрос выглядит вот так (в виде функции):

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
SELECT "A"."SigID", "A"."SigDateTimeAdded" - "A"."SampleDateTimeCatchedFirst" as "InternalSigCreationDuration"
    FROM (
        SELECT min("radar"."DateTimeCatched") as "SampleDateTimeCatchedFirst", "SigID", "sigs"."DateTimeAdded" as "SigDateTimeAdded"
        FROM "sigs"
            NATURAL JOIN "samples"
            NATURAL JOIN "radar"
            GROUP BY "SigID", "SigDateTimeAdded") "A"
    WHERE "SigDateTimeAdded" BETWEEN DateTimeFrom AND DateTimeTo AND
          "SigDateTimeAdded" >= "SampleDateTimeCatchedFirst";

И работает быстро. Но как, еще раз повторюсь, вытащить
Код: plaintext
WHERE "SigDateTimeAdded" BETWEEN DateTimeFrom AND DateTimeTo
наружу?запрос осмысленный. не понятно, что значит "в виде функции"? также не понятна ваша цель - "вытащить where наружу", для чего, если вы написали "работает быстро"? если хотите ускорить некоторый запрос, пишите ТОЧНЫЙ текст этого запроса и его EXPLAIN ANALYZE.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2008, 13:55
    #35520264
Robert Ayrapetyan
Robert Ayrapetyan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
В виде функции значит CREATE FUNCTION.... (DateTimeFrom, DateTimeTo)
Как в последнем сообщении.

А наружу, значит то же самое, но SELECT ... FROM view... WHERE SigDateTimeAdded BETWEEN
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2008, 14:31
    #35520384
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Неоптимальное использование WHERE в VIEW
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create table t1 ( type integer, value integer );
insert into t1 values ( 1 , 100 ), ( 1 , 200 ), ( 2 , 200 ), ( 2 , 300 );

create view v1 as select type, min(value) as min_value from t1 group by type;

 -- этот запрос возвращает (2,200)
select * from v1 where min_value> 150 ;

 -- это эквивалентный запрос, возвращает (2,200)
 -- у этих двух запросов одинаковые планы выполнения
select type, min(value) as min_value from t1 group by type having min(value)> 150 ;

 -- это другой запрос, возвращает (1,200), (2,200)
select type, min(value) as min_value from t1 where value> 150  group by type;

drop table t1 cascade;

так чего вы хотите? непонятно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неоптимальное использование WHERE в VIEW / 19 сообщений из 19, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]