postgresql 连接三个表时查询速度慢

7xzttuei  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(325)

我有房子和属性表。还有一个HouseAttribute模型通过主键连接房屋和属性表。
我想通过把每个属性作为一个新的列像下面的图像选择所有的房子与他们的属性。'location_attributes_toplu_ulaşıma_yakın'、'in_attributes_ankastre_mutfak'和'in_attributes_balkon'是来自属性表的属性。

SELECT 
    h.*,
    MAX(CASE WHEN attr.name = 'location_attributes_toplu_ulaşıma_yakın' THEN attr.value ELSE NULL END) AS location_attributes_toplu_ulaşıma_yakın,
    MAX(CASE WHEN attr.name = 'in_attributes_ankastre_mutfak' THEN attr.value ELSE NULL END) AS in_attributes_ankastre_mutfak,
    MAX(CASE WHEN attr.name = 'in_attributes_balkon' THEN attr.value ELSE NULL END) AS in_attributes_balkon
 FROM 
    houses h
 LEFT JOIN house_attributes ha ON h.id = ha.house_id
 LEFT JOIN attributes attr ON ha.attribute_id = attr.id
 GROUP BY 
    h.id
 ORDER BY inserted_at DESC;

上面的查询已经运行了30分钟，还没有结果。如何改进此查询？或者如何更改数据模型以使查询工作得更快？
\d+房屋

Table "public.houses"
         Column         |            Type             | Collation | Nullable |              Default               | Storage  | Stats target | Description 
------------------------+-----------------------------+-----------+----------+------------------------------------+----------+--------------+-------------
 id                     | integer                     |           | not null | nextval('houses_id_seq'::regclass) | plain    |              | 
 internal_id            | integer                     |           |          |                                    | plain    |              
 inserted_at            | timestamp without time zone |           |          |                                    | plain    |              | 
 is_active              | boolean                     |           |          |                                    | plain    |              | 
 listing_category       | character varying           |           |          |                                    | extended |              | 
 age                    | integer                     |           |          |                                    | plain    |              | 
Indexes:
    "houses_pkey" PRIMARY KEY, btree (id)
    "ix_houses_age" btree (age)

    "ix_houses_usage" btree (usage)
    "unique_house" UNIQUE CONSTRAINT, btree (internal_id, data_source)
Referenced by:
    TABLE "house_attributes" CONSTRAINT "house_attributes_house_id_fkey" FOREIGN KEY (house_id) REFERENCES houses(id)
Access method: heap

\d个属性和\d house_attributes

kelepir=# \d+ attributes
                                                     Table "public.attributes"
 Column |       Type        | Collation | Nullable |                Default                 | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id     | integer           |           | not null | nextval('attributes_id_seq'::regclass) | plain    |              | 
 name   | character varying |           |          |                                        | extended |              | 
 value  | character varying |           |          |                                        | extended |              | 
Indexes:
    "attributes_pkey" PRIMARY KEY, btree (id)
    "ix_attributes_id" btree (id)
    "ix_attributes_name" btree (name)
    "ix_attributes_value" btree (value)
Referenced by:
    TABLE "house_attributes" CONSTRAINT "house_attributes_attribute_id_fkey" FOREIGN KEY (attribute_id) REFERENCES attributes(id)
Access method: heap

kelepir=# \d+ house_attributes
                                                   Table "public.house_attributes"
    Column    |  Type   | Collation | Nullable |                   Default                    | Storage | Stats target | Description 
--------------+---------+-----------+----------+----------------------------------------------+---------+--------------+-------------
 id           | integer |           | not null | nextval('house_attributes_id_seq'::regclass) | plain   |              | 
 house_id     | integer |           |          |                                              | plain   |              | 
 attribute_id | integer |           |          |                                              | plain   |              | 
Indexes:
    "house_attributes_pkey" PRIMARY KEY, btree (id)
    "ix_house_attributes_id" btree (id)
Foreign-key constraints:
    "house_attributes_attribute_id_fkey" FOREIGN KEY (attribute_id) REFERENCES attributes(id)
    "house_attributes_house_id_fkey" FOREIGN KEY (house_id) REFERENCES houses(id)
Access method: heap

有大约170K行在房子表和3.house_attributes表中的500万行

填写DDL：

postgres@server:~$ pg_dump -d kelepir -s
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.9 (Debian 13.9-0+deb11u1)
-- Dumped by pg_dump version 13.9 (Debian 13.9-0+deb11u1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: alembic_version; Type: TABLE; Schema: public; Owner: mutlu
--

CREATE TABLE public.alembic_version (
    version_num character varying(32) NOT NULL
);

ALTER TABLE public.alembic_version OWNER TO mutlu;

--
-- Name: attributes; Type: TABLE; Schema: public; Owner: mutlu
--

CREATE TABLE public.attributes (
    id integer NOT NULL,
    name character varying,
    value character varying
);

ALTER TABLE public.attributes OWNER TO mutlu;

--
-- Name: attributes_id_seq; Type: SEQUENCE; Schema: public; Owner: mutlu
--

CREATE SEQUENCE public.attributes_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.attributes_id_seq OWNER TO mutlu;

--
-- Name: attributes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mutlu
--

ALTER SEQUENCE public.attributes_id_seq OWNED BY public.attributes.id;

--
-- Name: house_attributes; Type: TABLE; Schema: public; Owner: mutlu
--

CREATE TABLE public.house_attributes (
    id integer NOT NULL,
    house_id integer,
    attribute_id integer
);

ALTER TABLE public.house_attributes OWNER TO mutlu;

--
-- Name: house_attributes_id_seq; Type: SEQUENCE; Schema: public; Owner: mutlu
--

CREATE SEQUENCE public.house_attributes_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.house_attributes_id_seq OWNER TO mutlu;

--
-- Name: house_attributes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mutlu
--

ALTER SEQUENCE public.house_attributes_id_seq OWNED BY public.house_attributes.id;
--
-- Name: houses; Type: TABLE; Schema: public; Owner: mutlu
--

CREATE TABLE public.houses (
    id integer NOT NULL,
    internal_id integer,
    data_source character varying,
    url character varying NOT NULL,
    room smallint,
    living_room smallint,
    floor character varying,
    total_floor integer,
    bathroom smallint,
    net_sqm integer,
    gross_sqm integer,
    heating character varying,
    fuel character varying,
    usage character varying,
    credit character varying,
    deposit character varying,
    furnished character varying,
    version smallint,
    is_last_version boolean,
    latitude numeric,
    longitude numeric,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    realty_type character varying,
    currency character varying,
    district character varying,
    county character varying,
    city character varying,
    price integer,
    predicted_price integer,
    predicted_rental_price integer,
    inserted_at timestamp without time zone,
    is_active boolean,
    listing_category character varying,
    age integer
);

ALTER TABLE public.houses OWNER TO mutlu;

--
-- Name: houses_id_seq; Type: SEQUENCE; Schema: public; Owner: mutlu
--

CREATE SEQUENCE public.houses_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.houses_id_seq OWNER TO mutlu;

--
-- Name: houses_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mutlu
--

ALTER SEQUENCE public.houses_id_seq OWNED BY public.houses.id;
--
-- Name: attributes id; Type: DEFAULT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.attributes ALTER COLUMN id SET DEFAULT nextval('public.attributes_id_seq'::regclass);
--
-- Name: house_attributes id; Type: DEFAULT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.house_attributes ALTER COLUMN id SET DEFAULT nextval('public.house_attributes_id_seq'::regclass);
--
-- Name: houses id; Type: DEFAULT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.houses ALTER COLUMN id SET DEFAULT nextval('public.houses_id_seq'::regclass);
--
-- Name: alembic_version alembic_version_pkc; Type: CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.alembic_version
    ADD CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num);
--
-- Name: attributes attributes_pkey; Type: CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.attributes
    ADD CONSTRAINT attributes_pkey PRIMARY KEY (id);
--
-- Name: house_attributes house_attributes_pkey; Type: CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.house_attributes
    ADD CONSTRAINT house_attributes_pkey PRIMARY KEY (id);
--
-- Name: houses houses_pkey; Type: CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.houses
    ADD CONSTRAINT houses_pkey PRIMARY KEY (id);
--
-- Name: houses unique_house; Type: CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.houses
    ADD CONSTRAINT unique_house UNIQUE (internal_id, data_source);
--
-- Name: ix_attributes_id; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_attributes_id ON public.attributes USING btree (id);
--
-- Name: ix_attributes_name; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_attributes_name ON public.attributes USING btree (name);
--
-- Name: ix_attributes_value; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_attributes_value ON public.attributes USING btree (value);
--
-- Name: ix_house_attributes_id; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_house_attributes_id ON public.house_attributes USING btree (id);
--
-- Name: ix_houses_age; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_age ON public.houses USING btree (age);
--
-- Name: ix_houses_city; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_city ON public.houses USING btree (city);
--
-- Name: ix_houses_county; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_county ON public.houses USING btree (county);
--
-- Name: ix_houses_created_at; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_created_at ON public.houses USING btree (created_at);
--
-- Name: ix_houses_credit; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_credit ON public.houses USING btree (credit);
--
-- Name: ix_houses_currency; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_currency ON public.houses USING btree (currency);
--
-- Name: ix_houses_data_source; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_data_source ON public.houses USING btree (data_source);
--
-- Name: ix_houses_deposit; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_deposit ON public.houses USING btree (deposit);
--
-- Name: ix_houses_district; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_district ON public.houses USING btree (district);
--
-- Name: ix_houses_floor; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_floor ON public.houses USING btree (floor);
--
-- Name: ix_houses_fuel; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_fuel ON public.houses USING btree (fuel);
--
-- Name: ix_houses_furnished; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_furnished ON public.houses USING btree (furnished);
--
-- Name: ix_houses_gross_sqm; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_gross_sqm ON public.houses USING btree (gross_sqm);
--
-- Name: ix_houses_heating; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_heating ON public.houses USING btree (heating);
--
-- Name: ix_houses_id; Type: INDEX; Schema: public; Owner: mutlu
--

CREATE INDEX ix_houses_id ON public.houses USING btree (id);
--
-- Name: ix_houses_inserted_at; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_inserted_at ON public.houses USING btree (inserted_at);
--
-- Name: ix_houses_internal_id; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_internal_id ON public.houses USING btree (internal_id);
--
-- Name: ix_houses_latitude; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_latitude ON public.houses USING btree (latitude);
--
-- Name: ix_houses_listing_category; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_listing_category ON public.houses USING btree (listing_category);
--
-- Name: ix_houses_living_room; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_living_room ON public.houses USING btree (living_room);
--
-- Name: ix_houses_longitude; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_longitude ON public.houses USING btree (longitude);
--
-- Name: ix_houses_net_sqm; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_net_sqm ON public.houses USING btree (net_sqm);
--
-- Name: ix_houses_predicted_price; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_predicted_price ON public.houses USING btree (predicted_price);
--
-- Name: ix_houses_predicted_rental_price; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_predicted_rental_price ON public.houses USING btree (predicted_rental_price);
--
-- Name: ix_houses_price; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_price ON public.houses USING btree (price);
--
-- Name: ix_houses_realty_type; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_realty_type ON public.houses USING btree (realty_type);
--
-- Name: ix_houses_room; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_room ON public.houses USING btree (room);
--
-- Name: ix_houses_total_floor; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_total_floor ON public.houses USING btree (total_floor);
--
-- Name: ix_houses_updated_at; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_updated_at ON public.houses USING btree (updated_at);
--
-- Name: ix_houses_url; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE UNIQUE INDEX ix_houses_url ON public.houses USING btree (url);
--
-- Name: ix_houses_usage; Type: INDEX; Schema: public; Owner: mutlu
--
CREATE INDEX ix_houses_usage ON public.houses USING btree (usage);
--
-- Name: house_attributes house_attributes_attribute_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: mutlu
--

ALTER TABLE ONLY public.house_attributes
    ADD CONSTRAINT house_attributes_attribute_id_fkey FOREIGN KEY (attribute_id) REFERENCES public.attributes(id); 
--
-- Name: house_attributes house_attributes_house_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: mutlu
--
ALTER TABLE ONLY public.house_attributes
    ADD CONSTRAINT house_attributes_house_id_fkey FOREIGN KEY (house_id) REFERENCES public.houses(id);
--
-- PostgreSQL database dump complete
--

我在house_attributes添加了索引。house_id和house_attributes。attribute_id和VACUUM这三个表

结果：EXPLAIN(ANALYZE, VERBOSE, BUFFERS) query_above

"QUERY PLAN"
"Sort  (cost=391885.98..392316.69 rows=172284 width=344) (actual time=63582.785..64963.158 rows=172305 loops=1)"
"  Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age, (max((CASE WHEN ((attr.name)::text = 'location_attributes_toplu_ulaşıma_yakın'::text) THEN attr.value ELSE NULL::character varying END)::text)), (max((CASE WHEN ((attr.name)::text = 'in_attributes_ankastre_mutfak'::text) THEN attr.value ELSE NULL::character varying END)::text)), (max((CASE WHEN ((attr.name)::text = 'in_attributes_balkon'::text) THEN attr.value ELSE NULL::character varying END)::text))"
"  Sort Key: h.inserted_at DESC"
"  Sort Method: external merge  Disk: 47000kB"
"  Buffers: shared hit=807951 read=25123, temp read=8439 written=8462"
"  ->  Finalize GroupAggregate  (cost=1002.54..322722.07 rows=172284 width=344) (actual time=400.764..56653.290 rows=172305 loops=1)"
"        Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age, max((CASE WHEN ((attr.name)::text = 'location_attributes_toplu_ulaşıma_yakın'::text) THEN attr.value ELSE NULL::character varying END)::text), max((CASE WHEN ((attr.name)::text = 'in_attributes_ankastre_mutfak'::text) THEN attr.value ELSE NULL::character varying END)::text), max((CASE WHEN ((attr.name)::text = 'in_attributes_balkon'::text) THEN attr.value ELSE NULL::character varying END)::text)"
"        Group Key: h.id"
"        Buffers: shared hit=807951 read=25123"
"        ->  Gather Merge  (cost=1002.54..317553.55 rows=344568 width=344) (actual time=399.983..54275.450 rows=172305 loops=1)"
"              Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age, (PARTIAL max((CASE WHEN ((attr.name)::text = 'location_attributes_toplu_ulaşıma_yakın'::text) THEN attr.value ELSE NULL::character varying END)::text)), (PARTIAL max((CASE WHEN ((attr.name)::text = 'in_attributes_ankastre_mutfak'::text) THEN attr.value ELSE NULL::character varying END)::text)), (PARTIAL max((CASE WHEN ((attr.name)::text = 'in_attributes_balkon'::text) THEN attr.value ELSE NULL::character varying END)::text))"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              Buffers: shared hit=807951 read=25123"
"              ->  Partial GroupAggregate  (cost=2.51..276781.83 rows=172284 width=344) (actual time=150.838..29593.472 rows=57435 loops=3)"
"                    Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age, PARTIAL max((CASE WHEN ((attr.name)::text = 'location_attributes_toplu_ulaşıma_yakın'::text) THEN attr.value ELSE NULL::character varying END)::text), PARTIAL max((CASE WHEN ((attr.name)::text = 'in_attributes_ankastre_mutfak'::text) THEN attr.value ELSE NULL::character varying END)::text), PARTIAL max((CASE WHEN ((attr.name)::text = 'in_attributes_balkon'::text) THEN attr.value ELSE NULL::character varying END)::text)"
"                    Group Key: h.id"
"                    Buffers: shared hit=807951 read=25123"
"                    Worker 0:  actual time=141.474..28512.197 rows=57408 loops=1"
"                      JIT:"
"                        Functions: 20"
"                        Options: Inlining false, Optimization false, Expressions true, Deforming true"
"                        Timing: Generation 16.351 ms, Inlining 0.000 ms, Optimization 6.167 ms, Emission 124.438 ms, Total 146.956 ms"
"                      Buffers: shared hit=269123 read=8444"
"                    Worker 1:  actual time=138.299..28908.214 rows=57456 loops=1"
"                      JIT:"
"                        Functions: 20"
"                        Options: Inlining false, Optimization false, Expressions true, Deforming true"
"                        Timing: Generation 16.321 ms, Inlining 0.000 ms, Optimization 6.080 ms, Emission 124.646 ms, Total 147.047 ms"
"                      Buffers: shared hit=269153 read=8329"
"                    ->  Nested Loop Left Join  (cost=2.51..249058.76 rows=1485727 width=280) (actual time=150.379..26934.399 rows=1200690 loops=3)"
"                          Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age, attr.name, attr.value"
"                          Buffers: shared hit=807951 read=25123"
"                          Worker 0:  actual time=141.160..25833.573 rows=1205965 loops=1"
"                            Buffers: shared hit=269123 read=8444"
"                          Worker 1:  actual time=137.867..26202.234 rows=1200009 loops=1"
"                            Buffers: shared hit=269153 read=8329"
"                          ->  Parallel Index Scan using houses_pkey on public.houses h  (cost=0.42..12166.17 rows=71785 width=248) (actual time=149.976..7114.671 rows=57435 loops=3)"
"                                Output: h.id, h.internal_id, h.data_source, h.url, h.room, h.living_room, h.floor, h.total_floor, h.bathroom, h.net_sqm, h.gross_sqm, h.heating, h.fuel, h.usage, h.credit, h.deposit, h.furnished, h.version, h.is_last_version, h.latitude, h.longitude, h.created_at, h.updated_at, h.realty_type, h.currency, h.district, h.county, h.city, h.price, h.predicted_price, h.predicted_rental_price, h.inserted_at, h.is_active, h.listing_category, h.age"
"                                Buffers: shared hit=18024 read=7443"
"                                Worker 0:  actual time=140.832..5859.273 rows=57408 loops=1"
"                                  Buffers: shared hit=5985 read=2518"
"                                Worker 1:  actual time=137.423..5621.312 rows=57456 loops=1"
"                                  Buffers: shared hit=5503 read=2443"
"                          ->  Hash Right Join  (cost=2.09..5.01 rows=38 width=36) (actual time=0.136..0.300 rows=21 loops=172305)"
"                                Output: ha.house_id, attr.name, attr.value"
"                                Hash Cond: (attr.id = ha.attribute_id)"
"                                Buffers: shared hit=789927 read=17680"
"                                Worker 0:  actual time=0.140..0.303 rows=21 loops=57408"
"                                  Buffers: shared hit=263138 read=5926"
"                                Worker 1:  actual time=0.148..0.313 rows=21 loops=57456"
"                                  Buffers: shared hit=263650 read=5886"
"                                ->  Seq Scan on public.attributes attr  (cost=0.00..2.12 rows=112 width=36) (actual time=0.009..0.072 rows=112 loops=136041)"
"                                      Output: attr.id, attr.name, attr.value"
"                                      Buffers: shared hit=136041"
"                                      Worker 0:  actual time=0.009..0.071 rows=112 loops=45297"
"                                        Buffers: shared hit=45297"
"                                      Worker 1:  actual time=0.009..0.071 rows=112 loops=45479"
"                                        Buffers: shared hit=45479"
"                                ->  Hash  (cost=1.62..1.62 rows=38 width=8) (actual time=0.114..0.114 rows=21 loops=172305)"
"                                      Output: ha.house_id, ha.attribute_id"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 15kB"
"                                      Buffers: shared hit=653870 read=17680"
"                                      Worker 0:  actual time=0.118..0.118 rows=21 loops=57408"
"                                        Buffers: shared hit=217833 read=5926"
"                                      Worker 1:  actual time=0.126..0.126 rows=21 loops=57456"
"                                        Buffers: shared hit=218163 read=5886"
"                                      ->  Index Scan using ix_house_attributes_house_id on public.house_attributes ha  (cost=0.43..1.62 rows=38 width=8) (actual time=0.027..0.082 rows=21 loops=172305)"
"                                            Output: ha.house_id, ha.attribute_id"
"                                            Index Cond: (ha.house_id = h.id)"
"                                            Buffers: shared hit=653870 read=17680"
"                                            Worker 0:  actual time=0.029..0.086 rows=21 loops=57408"
"                                              Buffers: shared hit=217833 read=5926"
"                                            Worker 1:  actual time=0.029..0.094 rows=21 loops=57456"
"                                              Buffers: shared hit=218163 read=5886"
"Planning:"
"  Buffers: shared hit=24 read=4"
"Planning Time: 9.248 ms"
"JIT:"
"  Functions: 63"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 47.533 ms, Inlining 0.000 ms, Optimization 19.635 ms, Emission 403.362 ms, Total 470.530 ms"
"Execution Time: 65101.056 ms"
postgresql

1条答案

按热度按时间
ru9i0ody

ru9i0ody1#

您的多对多表（house_attributes）似乎没有优化搜索索引。house_attributes不需要id，你可以定义一个组合PK（house_id，attribute_id），它定义了字段的索引，它应该会使你的查询更好。
注意，索引应该根据你通常做的查询来定义，例如：
如果像这里所示那样进行查询

select h.*, ... 
from houses first
left join house_attributes ha
       on first.id = ha.house_id
left join attributes attr 
       on ha.attribute_id = attr.id

那么house_attributes中的索引，如（house_id，attribute_id）将是有用的
如果查询的顺序相反，例如： 选择h.*，... from attributes first left join house_attributes ha on www. example www.example.com = ha.attribute_id left join house h on www.example.com _id = www.example.com
更有用的索引将改为（attribute_id，house_id）
为了具有独立于查询顺序的搜索灵活性，可以使用两个索引，如
一个composed -〉（house_id，attribute_id）另一个仅用于第二字段attribute_id
然而，最好是有非常必要的索引，因为它们也有成本。.
用你的编码保持强大

赞(0）分享  回复(0）举报  2023-04-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com