查询加速

Question

我要加快下一个查询。

SELECT 'C', mpp_designation,periodic_number
FROM (
SELECT mpp_designation,periodic_number
FROM observations_optical_temp
WHERE mp_type = 'C'
GROUP BY mpp_designation,periodic_number) as a
WHERE not EXISTS(
    SELECT 1
    FROM mp_physical as b
    WHERE mp_type= 'C' AND b.designation = a.mpp_designation AND b.periodic_number = a.periodic_number);

但该计划表明，没有使用observations_optical_temp.mp_type上的索引。为什么？如何使用索引达到最优化的结果？

以下是对上述查询进行解释分析的结果。

     QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Merge Anti Join  (cost=45519.32..46134.57 rows=57095 width=7) (actual time=426.760..426.760 rows=0 loops=1)
   Output: 'C', observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
   Merge Cond: (((observations_optical_temp.mpp_designation)::text = (b.designation)::text) AND ((observations_optical_temp.periodic_number)::text = (b.periodic_number)::text))
   ->  Sort  (cost=42791.17..42981.49 rows=76127 width=7) (actual time=416.787..416.846 rows=1878 loops=1)
         Output: observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
         Sort Key: observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
         Sort Method: quicksort  Memory: 106kB
         ->  HashAggregate  (cost=35096.21..35857.48 rows=76127 width=7) (actual time=415.213..415.602 rows=1878 loops=1)
               Output: observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
               Group Key: observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
               ->  Seq Scan on pg_temp_2.observations_optical_temp  (cost=0.00..31289.86 rows=761269 width=7) (actual time=0.047..264.239 rows=761269 loops=1)
                     Output: observations_optical_temp.mpp_designation, observations_optical_temp.periodic_number
                     Filter: (observations_optical_temp.mp_type = 'C'::mp_type)
   ->  Sort  (cost=2728.15..2742.92 rows=5905 width=11) (actual time=8.301..8.533 rows=7333 loops=1)
         Output: b.designation, b.periodic_number
         Sort Key: b.designation, b.periodic_number
         Sort Method: quicksort  Memory: 415kB
         ->  Index Scan using "_I_mp_type" on public.mp_physical b  (cost=0.42..2358.27 rows=5905 width=11) (actual time=0.031..3.415 rows=7333 loops=1)
               Output: b.designation, b.periodic_number
               Index Cond: (b.mp_type = 'C'::mp_type)
 Planning time: 0.343 ms
 Execution time: 427.205 ms
(22 rows)

这是表的转储

CREATE TABLE public.observations_optical_temp(
    note_1 varchar,
    date timestamp NOT NULL,
    "RA" time NOT NULL,
    "Dec_degree" integer NOT NULL,
    "Dec" time NOT NULL,
    magnitude double precision,
    band varchar,
    observatory_code varchar(3),
    id_observatory integer,
    id_mpp integer,
    periodic_number varchar(8),
    mpp_designation varchar(30),
    mp_type public.mp_type
);
CREATE INDEX "_I_obs_temp" ON  observations_optical_temp  USING btree (observatory_code);
CREATE INDEX "_I_mp_type_temp1" ON observations_optical_temp USING btree(mpp_designation);
CREATE INDEX "_I_mp_type_temp2" ON observations_optical_temp USING btree(periodic_number);
CREATE INDEX "_I_mp_type_temp3" ON observations_optical_temp USING btree(mp_type);


CREATE TABLE public.mp_physical(
    id_mpp serial NOT NULL,
    id_comet_parts integer,
    "SPK_id" public.nonnegative_int,
    designation varchar(30),
    name varchar(100),
    prefix varchar,
    "is_NEO" bool,
    "H" double precision,
    "G" double precision,
    diameter public.nonnegative_double,
    extent varchar(30),
    extent_error public.nonnegative_double,
    geometric_albedo public.nonnegative_double,
    rot_per public.nonnegative_double,
    "GM" public.nonnegative_double,
    "BV" public.nonnegative_double,
    "UB" public.nonnegative_double,
    "spec_B" varchar(30),
    "spec_T" varchar(30),
    lca double precision,
    multiplicity public.nonnegative_int,
    polar_ang double precision,
    polar_slope_ang double precision,
    a double precision,
    b double precision,
    mass public.nonnegative_double,
    mp_type public.mp_type NOT NULL,
    periodic_number varchar(5),
    diameter_method_def varchar(200),
    discovery_info text,
    "H_sigma" public.nonnegative_double,
    "G_sigma" public.nonnegative_double,
    diameter_sigma public.nonnegative_double,
    geometric_albedo_sigma public.nonnegative_double,
    rot_per_sigma public.nonnegative_double,
    "GM_sigma" public.nonnegative_double,
    "BV_sigma" public.nonnegative_double,
    "UB_sigma" public.nonnegative_double,
    lca_sigma public.nonnegative_double,
    a_sigma public.nonnegative_double,
    b_sigma public.nonnegative_double,
    polar_ang_sigma public.nonnegative_double,
    mass_sigma public.nonnegative_double,
    CONSTRAINT "_C_id_ap" PRIMARY KEY (id_mpp)
);

CREATE INDEX "_I_name" ON  mp_physical  USING btree (name);
CREATE INDEX "_I_designation" ON mp_physical USING btree(mpp_designation);
CREATE INDEX "_I_periodic_number" ON mp_physical USING btree(periodic_number);
CREATE INDEX "_I_mp_type" ON mp_physical USING btree(mp_type);

Answer 1

您的查询可以重写为：

SELECT DISTINCT 'C', mpp_designation, periodic_number
FROM observations_optical_temp AS a
WHERE mp_type = 'C'
AND NOT EXISTS
(
    SELECT 1
    FROM mp_physical as b
    WHERE b.mp_type= 'C' AND b.designation = a.mpp_designation AND b.periodic_number = a.periodic_number
);

正如@a_horse_with_no_name所说，如果没有在100.000.000行上制定的计划，就很难给出其他建议。尝试前面的查询，告诉我们它是否更快(也是正确的)。