Поиск по 20 критериям

MiksIr · 24 Окт 2009

Так, стоп.. а там AND или OR по критериям требуется? Если AND - что-то не соображу как красиво это выбрать...

-~{}~ 24.10.09 00:16:

Если OR - то судя по explain поиск по массиву значительно веселее.

-~{}~ 26.10.09 15:09:

Фирмы (t1) - 1 000 000 записей. Услуги (t2) - 100. Таблица связей - у каждой фирмы все услуги (100 000 000 записей).

Код:

postgres=# \d t1
             Table "postgres.t1"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(200) |
 t2     | integer[]              |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
    "t1_t2_idx" gin (t2)

postgres=# \d t1to2
    Table "postgres.t1to2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id_t1  | integer |
 id_t2  | integer |
Indexes:
    "t1to2_t1_idx" btree (id_t1)
    "t1to2_t2_idx" btree (id_t2)

postgres=# select count(distinct t1.id) from t1 left join t1to2 on (t1.id=t1to2.id_t1) where t1to2.id_t2 IN (5,44,66,78,99);
  count
---------
 1000000
(1 row)

Time: 270618.443 ms

postgres=# select count(id) from t1 where t2 && '{5,44,66,78,99}'::integer[];
  count
---------
 1000000
(1 row)

Time: 34096.289 ms

dimagolov · 26 Окт 2009

MiksIr, по-моему тест некорректный. исходя из того, что индексы в нем вообще бесполезны (так как есть все связи), join естественным образом проигрывает из-за построения полной суперпозиции двух таблиц.
ты можешь распределить на каждую запись в t1 по 20-40-60 случайных с t2 и сделать замеры для этих случаев?

п.с. и left join там совсем лишний, должен быть inner join

MiksIr · 26 Окт 2009

ок, заполняем таблицу и переносим данные в поле массива

Код:

postgres=# insert into t1to2(id_t1,id_t2) SELECT g.n,round(random()*100) FROM generate_series(1,1000000) g(n), generate_series(1,10) f(n);

postgres=# update t1 set t2=(select ARRAY(SELECT t1to2.id_t2 FROM t1to2 WHERE t1to2.id_t1=t1.id));

Запросы

Код:

postgres=# select count(distinct t1.id) from t1 inner join t1to2 on (t1.id=t1to2.id_t1) where t1to2.id_t2 IN (5,44,66,78,99);
 count
--------
 401592
(1 row)

Time: 54582.257 ms

postgres=# select count(id) from t1 where t2 && '{5,44,66,78,99}'::integer[];
 count
--------
 401592
(1 row)

Time: 28918.132 ms

Правда следует учесть, что в случае с joinом приходится еще делать distinct, а это ни что иное, как сортировка. Без него получается

Код:

postgres=# select count(t1.id) from t1 inner join t1to2 on (t1.id=t1to2.id_t1) where t1to2.id_t2 IN (5,44,66,78,99);
 count
--------
 500762
(1 row)

Time: 33090.106 ms

-~{}~ 26.10.09 16:30:

В общем, использование массива с gin индексом как минимум не медленнее джойна, но ощутимо удобнее, особо когда нужно построить условие по AND (т.е. фирма где есть и услуга 10 и услуга 15).

dimagolov · 26 Окт 2009

подожди, а нафига там distinct? вернее в твоих запросах он нужен, но вообще-то ты делаешь проверку на "есть услуга 5 или 44 или...". для того, чтобы сделать такой запрос join-ить вообще ничего не нужно, достаточно сделать запрос по t1to2.

чтобы сделать and нужно сделать столько джоинов, сколько свойств нужно проверить, то есть в твоем случае:

Код:

select count(t1.id) from t1 
inner join t1to2 as att5 on (t1.id=att5.id_t1) and att5.id_t2 = 5
inner join t1to2 as att44 on (t1.id=att44.id_t1) and att44.id_t2 = 44
inner join t1to2 as att66 on (t1.id=att66.id_t1) and att66.id_t2 = 66
inner join t1to2 as att78 on (t1.id=att78.id_t1) and att78.id_t2 = 78
inner join t1to2 as att99 on (t1.id=att99.id_t1) and att99.id_t2 = 99;

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

Код:

select count(id) from t1 where t2 && '{5,44,66,78,99}'::integer[] = '{5,44,66,78,99}'::integer[];

MiksIr · 26 Окт 2009

Насчет AND - мне нравится все вариант с одним join-ном, групировкой и having по count. С массивом же будет

Код:

select count(id) from t1 where t2 @> '{5,44,66,78,99}'

Насчет дистинкта. Интересно, как это он не нужен. Даже если мы сделаем запрос по t1to2 - то получим все-равно дублирующиеся id фирм. И потом, нам нужно получить в реальных приложениях записи фирм с услугами, а не id фирм (id я использую в запросах просто для простоты).

dimagolov · 26 Окт 2009

MiksIr, ну нравиться может многое, но один join и group это временная таблица, в отличии от нескольких join. и что важнее, при нескольких join не нужно делать distinct, так как если нет дублирования идентичных связей, то в выборку попадут только те записи, которые имеют все нужные связи и только по одному разу. именно построение полной суперпозиции делает join в твоем исполнении медленным. попробуй мной вариант с несколькими join и массив с AND

MiksIr · 26 Окт 2009

В случае group - distinct тоже делать не нужно - у нас уже есть group

Давай не сваливать в одну кучу AND и OR выборки - они уж очень разные. В случае OR и JOIN-а от дублирующихся записей не избавиться без distinct-а.
Насчет AND

Код:

postgres=# select count(t1.id) from t1
postgres-# inner join t1to2 as att5 on (t1.id=att5.id_t1) and att5.id_t2 = 5
postgres-# inner join t1to2 as att44 on (t1.id=att44.id_t1) and att44.id_t2 = 44
postgres-# inner join t1to2 as att66 on (t1.id=att66.id_t1) and att66.id_t2 = 66
postgres-# inner join t1to2 as att78 on (t1.id=att78.id_t1) and att78.id_t2 = 78
postgres-# inner join t1to2 as att99 on (t1.id=att99.id_t1) and att99.id_t2 = 99;
Time: 33415.232 ms
postgres=# select count(id) from t1 where t2 @> '{5,44,66,78,99}';
Time: 27958.774 ms

dimagolov · 26 Окт 2009

MiksIr, в принципе теперь пришли к близкому результату. то есть для mysql (где нету полноценного массива) это очень даже решение

pavlodaranet · 1 Ноя 2009

А как вам такой вариант?

PHP:

if ($_POST['time']<="11:00:00"&& $_POST['time']>="08:00:00")
 {
$sql="SELECT *  FROM cur_place INNER JOIN options  ON cur_place.id = options.pid where";
$time=TIME($_POST['time']);
$sql.=" open_time <= $time";
}
if ($_POST['time']='') {
$sql="SELECT *  FROM cur_place INNER JOIN options  ON cur_place.id = options.pid where 1=1";
}

else  {
$sql="SELECT *  FROM cur_place INNER JOIN options  ON cur_place.id = options.pid where";
$time=TIME($_POST['time']);
$sql.=" close_time <= $time";
}

if ($_POST['sushi']) $sql.=" AND options.sushi='1'";
if ($_POST['pizza']) $sql.=" AND options.pizza='1'";
if ($_POST['karaoke']) $sql.=" AND options.karaoke='1'";
if ($_POST['draft_beer']) $sql.=" AND options.draft_beer='1'";
..........

dimagolov · 1 Ноя 2009

pavlodaranet, проблемы с этим решением уже называли в общем-то:
1. кол-во полей в options будет увеличиваться со временем
2. индексы по бинарному полю не эффективны

Lightning · 1 Ноя 2009

2. индексы по бинарному полю не эффективны

Вариант с кучей джоинов будет реально оптимальней? Это не шутка?

dimagolov · 1 Ноя 2009

Lightning, протестируй. MiksIr протестировал и показал, что в постгре джоины примерно равны по скорости массивам.

Поиск по 20 критериям

MiksIr

miksir@home:~$

dimagolov

Новичок

MiksIr

miksir@home:~$

dimagolov

Новичок

MiksIr

miksir@home:~$

dimagolov

Новичок

MiksIr

miksir@home:~$

dimagolov

Новичок

pavlodaranet

Новичок

dimagolov

Новичок

Lightning

Трудоголик

dimagolov

Новичок