create table categories (
id integer primary key,
parent integer references categories,
name varchar(50) not null
);
create table manufacturers (
id integer primary key,
name varchar(30) not null
);
create table goods (
id integer primary key,
category integer references categories,
manufacturer integer references manufacturers,
name varchar(50) not null
);
select
m.name,
count(*) as goods_count
from goods as g
inner join manufacturers as m
on g.manufacturer = m.id
where category = 1
group by m.name;
Как устроены фильтры в интернет магазинах?
Я это всё добавляю в базу ввиде json, чтобы потом по нему искать
Там все параметры товара ложатся в ячейку к товару.
melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::date AND dtime < '2016-12-01'::date)) inherits(events);
melkij=> insert into events2016m11 values ('2016-11-20');
INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Append (cost=0.00..38.25 rows=754 width=8) (actual time=0.013..0.013 rows=0 loops=1)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
-> Seq Scan on events2016m11 (cost=0.00..38.25 rows=753 width=8) (actual time=0.009..0.009 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
Rows Removed by Filter: 1
Planning time: 0.127 ms
Execution time: 0.032 ms
(8 строк)
melkij=> drop table events2016m11 ;
DROP TABLE
melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::timestamptz AND dtime < '2016-12-01'::timestamptz)) inherits(events);
CREATE TABLE
melkij=> insert into events2016m11 values ('2016-11-20');INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05'; QUERY PLAN
------------------------------------------------------------------------------------------------------
Append (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
Planning time: 0.301 ms
Execution time: 0.024 ms
(5 строк)
melkij=> show constraint_exclusion ;
constraint_exclusion
----------------------
partition
потом приводил каталоги к такому виду как в коде
