Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по xml-полям / 16 сообщений из 16, страница 1 из 1
22.07.2014, 15:43:32
    #38703072
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Уважаемые форумчане, подскажите как решить следующую задачу.
Есть таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE testxml
(
  doc xml,
  id serial NOT NULL,
  CONSTRAINT testxml_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE testxml
  OWNER TO postgres;


Заполнена записями:
Код: sql
1.
2.
3.
4.
id | doc
1  | <eee><aaa>qqq</aaa></eee>
2  | <eee><aaa>www</aaa><bbb>rrr</bbb></eee>
............


Надо найти те записи в которых отсутствует тег <bbb>
Я нашел два способа:
Код: sql
1.
2.
3.
4.
1. SELECT * from testxml
  where (xpath('/eee/bbb', doc))::text = '{}';
2. SELECT * from testxml
  where (xpath_exists('/eee/bbb', doc)) = 'f';


Но работает долго.

Итого:
1. Помогите создать индекс который бы использовался для данного поиска?
2. Может есть какие-то более правильные (красивые) способы добиться нужного результата?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 09:52:35
    #38703550
big-trot
big-trot
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Обработка XML всегда трудоемкая операция и я думаю, что никакие индексы на помогут (хотя могу ошибаться). Для вашей задачи регулярные выражения должны ускорить поиск.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 10:42:12
    #38703621
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
terc,

если поиск именно по условию отсутствия именно bbb частая - сделайте функциональный или условные индексы именно по этой ф-ии.

если это частный случай поисков "по тегам"-- то ответ может быть где-то в области полнотекстового поиска и т.п. неочевидных фич.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 11:23:37
    #38703676
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
big-trot,
Если создать индекс по значению тега <aaa> и искать по этому значению, то скорость равна поиску по индексированному полю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 11:25:04
    #38703679
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
qwwqterc,

если поиск именно по условию отсутствия именно bbb частая - сделайте функциональный или условные индексы именно по этой ф-ии.

если это частный случай поисков "по тегам"-- то ответ может быть где-то в области полнотекстового поиска и т.п. неочевидных фич.
Не подскажите как сделать "функциональный или условный индекс именно по этой ф-ии"

Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 13:23:49
    #38703844
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
terc <>
Не подскажите как сделать "функциональный или условный индекс именно по этой ф-ии"

Спасибо!
http://www.postgresql.org/docs/current/static/sql-commands.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2014, 17:05:47
    #38704160
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
qwwq,

Спасибо!
Для варианта:
Код: sql
1.
2.
SELECT * from testxml
  where (xpath_exists('/eee/bbb', doc)) = false;


Создал функциональный индекс:
Код: sql
1.
2.
3.
4.
CREATE INDEX idx1
  ON testxml
  USING btree
  (xpath_exists('/eee/bbb', doc))


и условный:
Код: sql
1.
2.
3.
4.
5.
CREATE INDEX idx2
  ON testxml
  USING btree
  (xpath_exists('/eee/bbb', doc))
  where (xpath_exists('/eee/bbb', doc)) = false



Срабатывает условный (idx2) - время запроса уменьшилось, функциональный (idx1) не срабатывает.
Интересно, почему?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2014, 10:43:32
    #38704589
Гость_0
Гость_0
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
tercИнтересно, почему?Потому что он меньше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2014, 17:54:59
    #38705300
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Гость_0,

Я пробовал их по очереди, а не два одновременно.
И функциональный не срабатывает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2014, 18:00:30
    #38705305
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
tercГость_0,

Я пробовал их по очереди, а не два одновременно.
И функциональный не срабатывает.

1)попробуйте сделатьс функциональным индексом
set seq_scan to 0;
explain analyze SELECT * from testxml
where (xpath_exists('/eee/bbb', doc)) = 'f';

и пришлите результат... без этого можно только гадать...

а да и результаты (тоже с этим проблемным индексом) для запросов:

explain analyze select count(*) from testxml;
и
explain analyze select count(*) from testxml where (xpath_exists('/eee/bbb', doc)) = 'f';
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2014, 18:26:23
    #38705339
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
terc,

после создания индекса не сделали
Код: sql
1.
analyze testxml;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2014, 16:57:09
    #38706219
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Maxim Boguk,

Для функционального индекса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
explain analyze SELECT * from testxml
  where (xpath_exists('/eee/bbb', doc)) = false;

SET enable_seqscan TO off;
Bitmap Heap Scan on testxml  (cost=9443.34..33005.09 rows=666667 width=36) (actual time=91.383..7098.417 rows=500000 loops=1)
  Filter: (NOT xpath_exists('/eee/bbb'::text, doc, '{}'::text[]))
  ->  Bitmap Index Scan on idx1  (cost=0.00..9276.68 rows=501900 width=0) (actual time=89.910..89.910 rows=500000 loops=1)
        Index Cond: (xpath_exists('/eee/bbb'::text, doc, '{}'::text[]) = false)
Total runtime: 7124.748 ms

SET enable_seqscan TO on;
Seq Scan on testxml  (cost=0.00..29788.00 rows=666667 width=36) (actual time=7405.740..14328.244 rows=500000 loops=1)
  Filter: (NOT xpath_exists('/eee/bbb'::text, doc, '{}'::text[]))
  Rows Removed by Filter: 500000
Total runtime: 14356.609 ms
===============
explain analyze select count(*) from testxml;

SET enable_seqscan TO off;
Aggregate  (cost=45771.43..45771.44 rows=1 width=0) (actual time=1894.117..1894.117 rows=1 loops=1)
  ->  Index Only Scan using testxml_pkey on testxml  (cost=0.42..43271.43 rows=1000000 width=0) (actual time=12.627..1779.161 rows=1000000 loops=1)
        Heap Fetches: 1000000
Total runtime: 1894.206 ms

SET enable_seqscan TO on;
Aggregate  (cost=29788.00..29788.01 rows=1 width=0) (actual time=228.065..228.065 rows=1 loops=1)
  ->  Seq Scan on testxml  (cost=0.00..27288.00 rows=1000000 width=0) (actual time=0.078..153.175 rows=1000000 loops=1)
Total runtime: 228.136 ms
=====================
explain analyze select count(*) from testxml where (xpath_exists('/eee/bbb', doc)) = false;

SET enable_seqscan TO off;
Aggregate  (cost=34671.76..34671.77 rows=1 width=0) (actual time=7036.644..7036.644 rows=1 loops=1)
  ->  Bitmap Heap Scan on testxml  (cost=9443.34..33005.09 rows=666667 width=0) (actual time=90.026..6963.090 rows=500000 loops=1)
        Filter: (NOT xpath_exists('/eee/bbb'::text, doc, '{}'::text[]))
        ->  Bitmap Index Scan on idx1  (cost=0.00..9276.68 rows=501900 width=0) (actual time=88.657..88.657 rows=500000 loops=1)
              Index Cond: (xpath_exists('/eee/bbb'::text, doc, '{}'::text[]) = false)
Total runtime: 7036.853 ms

SET enable_seqscan TO on;
Aggregate  (cost=31454.67..31454.68 rows=1 width=0) (actual time=14452.763..14452.763 rows=1 loops=1)
  ->  Seq Scan on testxml  (cost=0.00..29788.00 rows=666667 width=0) (actual time=7415.220..14372.177 rows=500000 loops=1)
        Filter: (NOT xpath_exists('/eee/bbb'::text, doc, '{}'::text[]))
        Rows Removed by Filter: 500000
Total runtime: 14452.836 ms



Ёш,

Да сделал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2014, 16:58:46
    #38706222
Asdtrjvtdr
Asdtrjvtdr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Здравствуйте! Прочитал Ваше предложение и у меня к Вам ответное предложение=) Не так давно в сети интернет появился новый интернет-портал, который на данный момент наполняется контентом. На портале можно онлайн проконсультироваться по разным вопросам (ИТ, юриспруденция, бизнес прочее), выставлять задания и проекты для выполнения, а также продавать или покупать продукты интеллектуального труда в магазине (дипломы, рефераты, программы прочее). В связи с тем, что ресурс находится на стадии наполнения контентом, я хотела бы предложить Вам разместить вышеуказанное объявление у нас на сайте. Это Вам ровным счетом стоить не будет. Если Вам интересно, то объявление будет размещено здесь http://www.outwork.net.ua/ Жду Вашего ответа!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2014, 17:02:10
    #38706231
Asdtrjvtdr
Asdtrjvtdr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Здравствуйте! Прочитал Ваше предложение и у меня к Вам ответное предложение=) Не так давно в сети интернет появился новый интернет-портал, который на данный момент наполняется контентом. На портале можно онлайн проконсультироваться по разным вопросам (ИТ, юриспруденция, бизнес прочее), выставлять задания и проекты для выполнения, а также продавать или покупать продукты интеллектуального труда в магазине (дипломы, рефераты, программы прочее). В связи с тем, что ресурс находится на стадии наполнения контентом, я хотела бы предложить Вам разместить вышеуказанное объявление у нас на сайте. Это Вам ровным счетом стоить не будет. Если Вам интересно, то объявление будет размещено здесь http://www.outwork.net.ua/ Жду Вашего ответа!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2014, 07:01:34
    #38706941
terc
terc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
Гость_0,

Я пробовал их по очереди, а не два одновременно.
И функциональный не срабатывает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2014, 10:26:12
    #38707030
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по xml-полям
terc,

Как вы видите ваш индекс может использоваться в запросах.
Просто база по своей статистике видит что под условие попадает 50% строк в таблице и считает что seq scan в таком случае будет быстрее (и в среднем она была бы даже права но xpath_exists операция очень дорогая). Тоесть проблема тут не в индексе а в распределении данных на которых вы тестируете.

Попробуйте потестировать когда под условие "Надо найти те записи в которых отсутствует тег <bbb>" попадает не половина строк а 1%... скорее всего функциональный индекс будет использован автоматически.

Если же вам надо принудительно использовать функциональный индекс на 50% попаданий - попробуйте поиграть с настройками random_page_cost в базе в стороно уменьшения (но как вы видите особо ускорения оно не дает).

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по xml-полям / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 457ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1998558]:
 0ms
get settings:
 6ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 171ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 202ms
total:  457ms
x
x
Закрыть


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