sql - 优化 SQL 查询

Question

使用 PostgreSQL 8.4 和这样的表:

create table log (
    id bigint primary key,
    first_sn bigint not null,
    last_sn bigint not null
);

其中 first_sn 和 last_sn 代表一个序列号范围，并且该表包含 > 100 万行，如果我想搜索序列号范围包含一个元素的所有行，我应该使用什么样的索引和查询序列号列表。

例如，对于列表 [5348491, 1230505, 5882233] 我目前正在做:

select 5348491, *
from log
where 5348491 between first_sn and last_sn
union
select 1230505, *
from log
where 1230505 between first_sn and last_sn
union
select 5882233, *
from log
where 5882233 between first_sn and last_sn;

但这有点慢。

编辑:这样的查询将花费大约 600 毫秒，我希望能够使用 >10k 序列号的列表进行搜索。

由于有人请求，这里是真实的表、查询和解释分析(我犹豫了，因为所有的列名都是西类牙语，但在前面的例子中 'id' 在这里是 'movimiento_id'，'first_sn' 是'serial_inicial' 和 'last_sn' 将是 'serial_final'。'tipo_movimiento' 是事件的类型，实际上它只是进一步过滤结果集的一种方式):

    CREATE TABLE movimiento
(
  movimiento_id bigserial NOT NULL,
  serial_inicial bigint NOT NULL,
  serial_final bigint NOT NULL,
  serial_chip bigint,
  numero_telefono text,
  fecha_movimiento timestamp without time zone DEFAULT now(),
  producto_id integer NOT NULL,
  usuario_id integer NOT NULL,
  factura_proveedor text,
  fecha_ingreso date,
  fecha_venta date,
  vendedor_id integer,
  cliente_id integer,
  tipo_movimiento text NOT NULL,
  costo numeric(12,4),
  precio numeric(10,2),
  descuento double precision,
  bodega_id integer NOT NULL DEFAULT 1,
  fecha_activo timestamp without time zone,
  factura text,
  envio text,
  documento text,
  bodega_id_origen integer,
  fecha date,
  traslado_id integer,
  detalle_factura_id bigint,
  es_venta boolean DEFAULT false,
  CONSTRAINT movimiento_pkey PRIMARY KEY (movimiento_id ),
  CONSTRAINT movimiento_bodega_id_fkey FOREIGN KEY (bodega_id)
      REFERENCES bodega (bodega_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_bodega_id_origen_fkey FOREIGN KEY (bodega_id_origen)
      REFERENCES bodega (bodega_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_cliente_id_fkey FOREIGN KEY (cliente_id)
      REFERENCES cliente (cliente_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_producto_id_fkey FOREIGN KEY (producto_id)
      REFERENCES producto (producto_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_usuario_id_fkey FOREIGN KEY (usuario_id)
      REFERENCES usuario (usuario_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_vendedor_id_fkey FOREIGN KEY (vendedor_id)
      REFERENCES vendedor (vendedor_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT movimiento_check CHECK (serial_final >= serial_inicial),
  CONSTRAINT movimiento_costo_check CHECK (costo >= 0::numeric),
  CONSTRAINT movimiento_descuento_check CHECK (descuento >= 0::double precision),
  CONSTRAINT movimiento_precio_check CHECK (precio >= 0::numeric),
  CONSTRAINT movimiento_tipo_movimiento_check CHECK (tipo_movimiento = ANY (ARRAY['Ingresado'::text, 'Vendido'::text, 'Entregado'::text, 'Regresado'::text, 'Eliminado'::text, 'Devuelto'::text, 'Inconforme'::text, 'Trasladado'::text, 'Consignado'::text, 'Devolucion Consignado'::text, 'Activado'::text, 'Devolucion Claro'::text, 'Asignado'::text, 'Fusion-Sale'::text, 'Fusion'::text, 'Separacion-Sale'::text, 'Separacion'::text]))
)
WITH (
  OIDS=TRUE
);

这是查询:

    explain analyze select 869461009867643, *
from movimiento
where (869461009867643 between serial_inicial and serial_final)
and tipo_movimiento = 'Ingresado'
union all
select 12121001477546, *
from movimiento
where 12121001477546 between serial_inicial and serial_final
and tipo_movimiento = 'Ingresado'
union all
select 354689040208615, *
from movimiento
where 354689040208615 between serial_inicial and serial_final
and tipo_movimiento = 'Ingresado';

解释分析:

Append  (cost=7542.94..185580.33 rows=232322 width=165) (actual time=93.222..571.928 rows=4 loops=1)
  ->  Bitmap Heap Scan on movimiento  (cost=7542.94..61089.00 rows=90645 width=165) (actual time=93.220..206.248 rows=1 loops=1)
        Recheck Cond: (tipo_movimiento = 'Ingresado'::text)
        Filter: ((869461009867643::bigint >= serial_inicial) AND (869461009867643::bigint <= serial_final))
        ->  Bitmap Index Scan on tipo_movimiento_index  (cost=0.00..7520.28 rows=375432 width=0) (actual time=66.445..66.445 rows=372409 loops=1)
              Index Cond: (tipo_movimiento = 'Ingresado'::text)
  ->  Bitmap Heap Scan on movimiento  (cost=7534.24..61080.30 rows=55815 width=165) (actual time=84.364..179.571 rows=2 loops=1)
        Recheck Cond: (tipo_movimiento = 'Ingresado'::text)
        Filter: ((12121001477546::bigint >= serial_inicial) AND (12121001477546::bigint <= serial_final))
        ->  Bitmap Index Scan on tipo_movimiento_index  (cost=0.00..7520.28 rows=375432 width=0) (actual time=60.282..60.282 rows=372409 loops=1)
              Index Cond: (tipo_movimiento = 'Ingresado'::text)
  ->  Bitmap Heap Scan on movimiento  (cost=7541.75..61087.81 rows=85862 width=165) (actual time=173.876..186.082 rows=1 loops=1)
        Recheck Cond: (tipo_movimiento = 'Ingresado'::text)
        Filter: ((354689040208615::bigint >= serial_inicial) AND (354689040208615::bigint <= serial_final))
        ->  Bitmap Index Scan on tipo_movimiento_index  (cost=0.00..7520.28 rows=375432 width=0) (actual time=60.294..60.294 rows=372409 loops=1)
              Index Cond: (tipo_movimiento = 'Ingresado'::text)
Total runtime: 572.138 ms

下面是对a_horse_with_no_name 的例子的解释分析:

    Nested Loop  (cost=7614.18..98703.44 rows=125144 width=173) (actual time=629.373..2919.334 rows=4 loops=1)
  Join Filter: ((lista.serie >= movimiento.serial_inicial) AND (lista.serie <= movimiento.serial_final))
  CTE lista
    ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=8) (actual time=0.012..0.033 rows=3 loops=1)
  ->  Bitmap Heap Scan on movimiento  (cost=7614.14..59283.04 rows=375432 width=165) (actual time=110.909..460.563 rows=372409 loops=1)
        Recheck Cond: (tipo_movimiento = 'Ingresado'::text)
        ->  Bitmap Index Scan on tipo_movimiento_index  (cost=0.00..7520.28 rows=375432 width=0) (actual time=107.182..107.182 rows=372409 loops=1)
              Index Cond: (tipo_movimiento = 'Ingresado'::text)
  ->  CTE Scan on lista  (cost=0.00..0.06 rows=3 width=8) (actual time=0.001..0.003 rows=3 loops=372409)
Total runtime: 2919.514 ms

因此结合 a_horse_with_no_name 和 Craig Ringer 的建议，搜索三个序列号的时间不到 350 毫秒。尝试使用 10k 并在 3s+ 内完成:

create temporary table lista (
    serie bigint
) on commit drop;
create index lista_index on lista using btree (serie);
insert into lista (select distinct serial_inicial from movimiento limit 10000);
analyze lista;
select serie, movimiento.*
from movimiento join lista on serie between serial_inicial and serial_final
where tipo_movimiento = 'Ingresado';

Answer 1

如果您真的不需要提供的值匹配的信息，您可以使用简单的 OR:

select *
from log
where (5348491 between first_sn and last_sn)
   or (1230505 between first_sn and last_sn)
   or (5882233 between first_sn and last_sn);

另一种选择是:

with sn_list (sn) as (
   values (5348491), (1230505), (5882233)
)
select ids.sn as searched_value,
       log.*
from log
  join sn_list on sn_list.sn between log.first_sn and log.last_sn;

虽然我认为这些解决方案中的任何一个实际上都不会扩展到 10k 值以进行比较。

(我假设你在两个 sn 列上都有一个索引)