在PostgreSQL中优化SQL查询

Question

在PostgreSQL中优化SQL查询

5

我有一个SQL查询，非常慢，而且在PostgreSQL中没有使用索引。

查询语句：

select s.id as id,
   utt.count as menciones,
   ut.usuario as user_twitter,
   ut.id as id_user_twitter
from busquedas_busqueda bu
  join dashboards_tablaagregadatwitterdiaria s on s.busqueda_id = bu.id
  join dashboards_usuariotwitter_tablaagregadatwitterdiaria utt on utt.tabla_id = s.id
  join busquedas_usuario_twitter ut on ut.id = utt.usuario_id     
where bu.cuenta_id=74 and
  s.fecha_id >= 20150811 AND s.fecha_id <= 20150909 AND
  bu.corporativa=false
group by s.id, utt.count, ut.usuario,ut.id

查询计划如下：

Group  (cost=178870.19..179050.19 rows=14400 width=29)
  ->  Sort  (cost=178870.19..178906.19 rows=14400 width=29)
        Sort Key: s.id, utt.count, ut.usuario, ut.id 
         ->  Nested Loop  (cost=4657.19..177875.60 rows=14400 width=29) 
             ->  Hash Join  (cost=4657.19..52048.58 rows=14400 width=16)
                 Hash Cond: (utt.tabla_id = s.id) 
                 ->  Seq Scan on dashboards_usuariotwitter_tablaagregadatwitterdiaria utt  (cost=0.00..39092.46 rows=2174646 width=16)
                    ->  Hash  (cost=4645.87..4645.87 rows=906 width=4)
                          ->  Hash Join  (cost=13.73..4645.87 rows=906 width=4) 
                               Hash Cond: (s.busqueda_id = bu.id) 
                               ->  Seq Scan on dashboards_tablaagregadatwitterdiaria s  (cost=0.00..4111.32 rows=136470 width=8) 
                                      Filter: ((fecha_id >= 20150811) AND (fecha_id <= 20150909)) 
                                ->  Hash  (cost=13.69..13.69 rows=3 width=4) 
                                      ->  Bitmap Heap Scan on busquedas_busqueda bu  (cost=4.30..13.69 rows=3 width=4) 
                                           Recheck Cond: (cuenta_id = 74) 
                                             Filter: (NOT corporativa) 
                                               ->  Bitmap Index Scan on busquedas_busqueda_cuenta_id  (cost=0.00..4.30 rows=7 width=0)  
                                                  Index Cond: (cuenta_id = 74) 
              ->  Index Scan using busquedas_usuario_twitter_pkey on busquedas_usuario_twitter ut  (cost=0.00..8.73 rows=1 width=17) 
                 Index Cond: (id = utt.usuario_id)

模型如下：

CREATE TABLE busquedas_busqueda
(
  id serial NOT NULL,
  nombre character varying(150) NOT NULL,
  cuenta_id integer,
  busqueda_real text NOT NULL,
  terminos_asociados text NOT NULL,
  terminos_excluyentes text NOT NULL,
  color character varying(7) NOT NULL,
  corporativa boolean,
  busqueda character varying(150) NOT NULL,
  predeterminada boolean,
  tipo_busqueda integer,
 CONSTRAINT busquedas_busqueda_pkey PRIMARY KEY (id),
 CONSTRAINT cuenta_id_refs_id_6ced8d50 FOREIGN KEY (cuenta_id)
  REFERENCES usuarios_cuenta (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
 )

CREATE INDEX busquedas_busqueda_cuenta_id
ON busquedas_busqueda
USING btree
(cuenta_id);

CREATE TABLE busquedas_usuario_twitter
(
  id serial NOT NULL,
  nombre character varying(150),
  usuario character varying(150) NOT NULL,
  biografia text,
  numero_seguidores integer,
  numero_seguidos integer,
  localizacion character varying(100),
  zona_horaria character varying(100),
  fecha_actualizacion timestamp with time zone,
  numero_tweets double precision,
  lenguaje character varying(10),
  imagen character varying(300),
  id_twitter bigint,
  activo boolean NOT NULL,
  visibilidad_id integer,
  CONSTRAINT busquedas_usuario_twitter_pkey PRIMARY KEY (id),
  CONSTRAINT visibilidad_id_refs_id_5405f268 FOREIGN KEY (visibilidad_id)
  REFERENCES dashboards_visibilidad_twitter (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT busquedas_usuario_twitter_id_twitter_key UNIQUE (id_twitter),
  CONSTRAINT busquedas_usuario_twitter_usuario_uniq UNIQUE (usuario)
 )

 CREATE INDEX busquedas_usuario_twitter_visibilidad_id
 ON busquedas_usuario_twitter
 USING btree
 (visibilidad_id);


CREATE TABLE dashboards_tablaagregadatwitterdiaria
(
  id serial NOT NULL,
  busqueda_id integer NOT NULL,
  source_id integer NOT NULL,
  fecha_id integer,
  visibilidad_id integer NOT NULL,
  tipo_de_follower_id integer,
  menciones integer,
  tipo_menciones_id integer,
  sentimiento double precision NOT NULL,
  tipo_tweet character varying(50) NOT NULL,
  termino_encontrado character varying(200),
  pais_id integer,
  idioma_id integer,
  CONSTRAINT dashboards_tablaagregadatwitterdiaria_pkey PRIMARY KEY (id),
  CONSTRAINT busqueda_id_refs_id_3c681db2 FOREIGN KEY (busqueda_id)
  REFERENCES busquedas_busqueda (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT fecha_id_refs_id_614d33db FOREIGN KEY (fecha_id)
  REFERENCES dashboards_tiempo (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT idioma_id_refs_id_9cc00494 FOREIGN KEY (idioma_id)
  REFERENCES usuarios_idioma (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT pais_id_refs_id_7a261136 FOREIGN KEY (pais_id)
  REFERENCES usuarios_pais (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT source_id_refs_id_eca486c8 FOREIGN KEY (source_id)
  REFERENCES busquedas_source (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT tipo_de_follower_id_refs_id_a71669d3 FOREIGN KEY      (tipo_de_follower_id)
  REFERENCES dashboards_tipo_followers (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT tipo_menciones_id_refs_id_027149b5 FOREIGN KEY (tipo_menciones_id)
  REFERENCES dashboards_tipo_mencion (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT visibilidad_id_refs_id_c3fc21ae FOREIGN KEY (visibilidad_id)
  REFERENCES dashboards_visibilidad_twitter (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT dashboards_tablaagregadatwitterdiaria_busqueda_id_50f275f1_uniq      UNIQUE (busqueda_id, fecha_id, visibilidad_id, tipo_de_follower_id,  tipo_menciones_id, tipo_tweet, termino_encontrado, pais_id, idioma_id,  sentimiento)
)

CREATE INDEX dashboards_tablaagregadatwitterdiaria_busqueda_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(busqueda_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_busqueda_idfecha_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(busqueda_id, fecha_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_fecha_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(fecha_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_idioma_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(idioma_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_pais_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(pais_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_source_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(source_id);


CREATE INDEX dashboards_tablaagregadatwitterdiaria_tipo_de_follower_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(tipo_de_follower_id);

CREATE INDEX dashboards_tablaagregadatwitterdiaria_tipo_menciones_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(tipo_menciones_id);

 CREATE INDEX dashboards_tablaagregadatwitterdiaria_visibilidad_id
ON dashboards_tablaagregadatwitterdiaria
USING btree
(visibilidad_id);



CREATE TABLE dashboards_usuariotwitter_tablaagregadatwitterdiaria
(
  id serial NOT NULL,
  tabla_id integer NOT NULL,
  usuario_id integer NOT NULL,
  nombre text,
  count double precision,
  CONSTRAINT dashboards_usuariotwitter_tablaagregadatwitterdiaria_pkey     PRIMARY KEY (id),
   CONSTRAINT tabla_id_refs_id_120e7c9e FOREIGN KEY (tabla_id)
  REFERENCES dashboards_tablaagregadatwitterdiaria (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT usuario_id_refs_id_4cb3ad7e FOREIGN KEY (usuario_id)
  REFERENCES busquedas_usuario_twitter (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT dashboards_usuariotwitter_tablaagregadat_tabla_id_35fb3a75_uniq    UNIQUE (tabla_id, usuario_id, nombre)
  )

 CREATE INDEX dashboards_usuariotwitter_tablaagregadatwitterdiaria_tabla_id
 ON dashboards_usuariotwitter_tablaagregadatwitterdiaria
 USING btree
 (tabla_id);

CREATE INDEX dashboards_usuariotwitter_tablaagregadatwitterdiaria_usuario_id
ON dashboards_usuariotwitter_tablaagregadatwitterdiaria
USING btree
(usuario_id);

我尝试创建索引，并以几种不同的方式构建查询，但我没有得到优化，需要帮助吗？

- Román González

4

表定义，请包括索引/键。当没有聚合函数时，为什么要使用GROUP BY？（也许使用SELECT DISTINCT更快？） - jarlh

3

请不要使用隐式连接（implicit joins）。适当的连接是带有ON子句（clause）的连接。 - Jakub Kania

我刚刚更新了这篇帖子，加入了另一个与适当的连接有关的查询，但问题仍然存在。我需要按分组获取数据。表定义？所有表中的ID都是键，并且现在已更新帖子以包含索引。谢谢。 - Román González

2

这个fecha_id看起来像是一个日期类型。为什么你要把一个日期字段放到一个整数里面呢？如果你经常对它进行（有选择性的）范围搜索，那么它可能需要一个索引。 - joop

1

添加了表定义和索引。 - Román González

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rohit Gaikwad · Accepted Answer

试试这个

SELECT s.id as id, ht.count as menciones, bh.texto as hastag
FROM busquedas_hastag bh
INNER JOIN dashboards_hastag_tablaagregadatwitterdiaria ht ON ht.hastag_id=bh.id
INNER JOIN dashboards_tablaagregadatwitterdiaria s ON s.id=ht.tabla_id
INNER JOIN busquedas_busqueda bu ON s.busqueda_id=bu.id
WHERE bu.corporativa=false AND bu.cuenta_id=74
s.fecha_id >= 20150811 AND s.fecha_id <= 20150909
GROUP BY s.id, ht.count, hastag

如果您对这个解决方案有任何问题，请让我知道。