поиск по xml-полям / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по xml-полям

16 сообщений из 16, страница 1 из 1

поиск по xml-полям

#38703072

terc

Гость

Уважаемые форумчане, подскажите как решить следующую задачу.
Есть таблица:

Код: 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

22.07.2014, 15:43:32

| Ответить | Цитировать | Написать

поиск по xml-полям

#38703550

big-trot

Гость

Обработка XML всегда трудоемкая операция и я думаю, что никакие индексы на помогут (хотя могу ошибаться). Для вашей задачи регулярные выражения должны ускорить поиск.

...

Рейтинг:

0 / 0

23.07.2014, 09:52:35

| Ответить | Цитировать | Написать

поиск по xml-полям

#38703621

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

terc,

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

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

...

Рейтинг:

0 / 0

23.07.2014, 10:42:12

| Ответить | Цитировать | Написать

поиск по xml-полям

#38703676

terc

Гость

big-trot,
Если создать индекс по значению тега <aaa> и искать по этому значению, то скорость равна поиску по индексированному полю.

...

Рейтинг:

0 / 0

23.07.2014, 11:23:37

| Ответить | Цитировать | Написать

поиск по xml-полям

#38703679

terc

Гость

qwwqterc,

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

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

Спасибо!

...

Рейтинг:

0 / 0

23.07.2014, 11:25:04

| Ответить | Цитировать | Написать

поиск по xml-полям

#38703844

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

terc <>
Не подскажите как сделать "функциональный или условный индекс именно по этой ф-ии"

Спасибо!
http://www.postgresql.org/docs/current/static/sql-commands.html

...

Рейтинг:

0 / 0

23.07.2014, 13:23:49

| Ответить | Цитировать | Написать

поиск по xml-полям

#38704160

terc

Гость

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

23.07.2014, 17:05:47

| Ответить | Цитировать | Написать

поиск по xml-полям

#38704589

Гость_0

Гость

tercИнтересно, почему?Потому что он меньше.

...

Рейтинг:

0 / 0

24.07.2014, 10:43:32

| Ответить | Цитировать | Написать

поиск по xml-полям

#38705300

terc

Гость

Гость_0,

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

...

Рейтинг:

0 / 0

24.07.2014, 17:54:59

| Ответить | Цитировать | Написать

поиск по xml-полям

#38705305

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

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:00:30

| Ответить | Цитировать | Написать

поиск по xml-полям

#38705339

Ёш

Участник

Сообщения: 2 921

Рейтинг: 0 / 0

terc,

после создания индекса не сделали

Код: sql

analyze testxml;

...

Рейтинг:

0 / 0

24.07.2014, 18:26:23

| Ответить | Цитировать | Написать

поиск по xml-полям

#38706219

terc

Гость

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:57:09

| Ответить | Цитировать | Написать

поиск по xml-полям

#38706222

Asdtrjvtdr

Гость

Здравствуйте! Прочитал Ваше предложение и у меня к Вам ответное предложение=) Не так давно в сети интернет появился новый интернет-портал, который на данный момент наполняется контентом. На портале можно онлайн проконсультироваться по разным вопросам (ИТ, юриспруденция, бизнес прочее), выставлять задания и проекты для выполнения, а также продавать или покупать продукты интеллектуального труда в магазине (дипломы, рефераты, программы прочее). В связи с тем, что ресурс находится на стадии наполнения контентом, я хотела бы предложить Вам разместить вышеуказанное объявление у нас на сайте. Это Вам ровным счетом стоить не будет. Если Вам интересно, то объявление будет размещено здесь http://www.outwork.net.ua/ Жду Вашего ответа!

...

Рейтинг:

0 / 0

25.07.2014, 16:58:46

| Ответить | Цитировать | Написать

поиск по xml-полям

#38706231

Asdtrjvtdr

Гость

...

Рейтинг:

0 / 0

25.07.2014, 17:02:10

| Ответить | Цитировать | Написать

поиск по xml-полям

#38706941

terc

Гость

Гость_0,

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

...

Рейтинг:

0 / 0

28.07.2014, 07:01:34

| Ответить | Цитировать | Написать

поиск по xml-полям

#38707030

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

terc,

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

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

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

--Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

28.07.2014, 10:26:12

| Ответить | Цитировать | Написать

16 сообщений из 16, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по xml-полям

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=38705339&tid=1998558]:	0ms
get settings:	8ms
get forum list:	17ms
check forum access:	4ms
check topic access:	4ms
track hit:	201ms
get topic data:	12ms
get forum data:	3ms
get page messages:	72ms
get tp. blocked users:	2ms
others:	233ms

total:	556ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы