|
помогите понять логику оптимизатора
#39204365
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Белый город
Сообщения: 16 205
|
|
Запрос:
1.
2.
3.
4.
5.
6.
EXPLAIN ANALYSE
SELECT p0_.id AS id_0, p0_.name AS name_1, p0_.surname AS surname_2, f1_.id AS id_3, f1_.username_canonical AS username_canonical_4, s2_.name AS name_5
FROM auth.person p0_
LEFT JOIN auth.fos_user f1_ ON p0_.id = f1_.person_id
LEFT JOIN structure.station s2_ ON p0_.station_id = s2_.id
ORDER BY surname_2 ASC LIMIT 50 OFFSET 0
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
CREATE TABLE auth.fos_user
(
id uuid NOT NULL,
username character varying(255) NOT NULL, -- (DC2Type:string)
username_canonical character varying(255) NOT NULL, -- (DC2Type:string)
email character varying(255) NOT NULL, -- (DC2Type:string)
email_canonical character varying(255) NOT NULL, -- (DC2Type:string)
enabled boolean NOT NULL,
salt character varying(255) NOT NULL, -- (DC2Type:string)
password character varying(255) NOT NULL, -- (DC2Type:string)
last_login timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
locked boolean NOT NULL,
expired boolean NOT NULL,
expires_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
confirmation_token character varying(255) DEFAULT NULL::character varying, -- (DC2Type:string)
password_requested_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
roles text NOT NULL, -- (DC2Type:array)
credentials_expired boolean NOT NULL,
credentials_expire_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
person_id uuid,
CONSTRAINT fos_user_pkey PRIMARY KEY (id),
CONSTRAINT fk_56583469217bbb47 FOREIGN KEY (person_id)
REFERENCES auth.person (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE auth.fos_user
OWNER TO veterinary;
CREATE UNIQUE INDEX uniq_5658346992fc23a8
ON auth.fos_user
USING btree
(username_canonical COLLATE pg_catalog."default");
CREATE UNIQUE INDEX uniq_56583469a0d96fbf
ON auth.fos_user
USING btree
(email_canonical COLLATE pg_catalog."default");
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
-- Table: auth.person
-- DROP TABLE auth.person;
CREATE TABLE auth.person
(
id uuid NOT NULL,
login character varying(255) NOT NULL,
password character varying(64) NOT NULL,
name character varying(255) NOT NULL,
surname character varying(255) NOT NULL,
station_id uuid,
subdivision_id uuid,
patronymic character varying(255) DEFAULT NULL::character varying,
roles character varying(255)[],
vsd_station_id uuid,
vsd_subdivision_id uuid,
is_invalid boolean NOT NULL DEFAULT false,
ogvn_id uuid,
"position" character varying(1000),
phone character varying(255),
email character varying(255),
CONSTRAINT person_pkey PRIMARY KEY (id),
CONSTRAINT fk_5e60ad0b26595f8e FOREIGN KEY (vsd_subdivision_id)
REFERENCES vsd.subdivision (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_5e60ad0b2bd68af6 FOREIGN KEY (vsd_station_id)
REFERENCES vsd.station (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE auth.person
OWNER TO veterinary;
CREATE INDEX idx_5e60ad0b21bdb235
ON auth.person
USING btree
(station_id);
CREATE INDEX idx_5e60ad0b26595f8e
ON auth.person
USING btree
(vsd_subdivision_id);
CREATE INDEX idx_5e60ad0b2bd68af6
ON auth.person
USING btree
(vsd_station_id);
CREATE INDEX person_surname_index
ON auth.person
USING btree
(surname COLLATE pg_catalog."default");
CREATE UNIQUE INDEX uniq_1549b3a2aa08cb10
ON auth.person
USING btree
(login COLLATE pg_catalog."default");
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE structure.station
(
id uuid NOT NULL,
name character varying(512) NOT NULL,
region integer,
ogvn_id uuid,
CONSTRAINT station_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE structure.station
OWNER TO veterinary;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
"Limit (cost=749.55..749.68 rows=50 width=141) (actual time=25.648..25.661 rows=50 loops=1)"
" -> Sort (cost=749.55..761.16 rows=4644 width=141) (actual time=25.644..25.650 rows=50 loops=1)"
" Sort Key: p0_.surname"
" Sort Method: top-N heapsort Memory: 23kB"
" -> Hash Left Join (cost=273.34..595.28 rows=4644 width=141) (actual time=7.482..17.169 rows=4644 loops=1)"
" Hash Cond: (p0_.station_id = s2_.id)"
" -> Hash Left Join (cost=271.10..575.62 rows=4644 width=106) (actual time=7.401..14.974 rows=4644 loops=1)"
" Hash Cond: (p0_.id = f1_.person_id)"
" -> Seq Scan on person p0_ (cost=0.00..217.44 rows=4644 width=55) (actual time=0.012..2.990 rows=4644 loops=1)"
" -> Hash (cost=210.49..210.49 rows=4849 width=67) (actual time=7.362..7.362 rows=4644 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 421kB"
" -> Seq Scan on fos_user f1_ (cost=0.00..210.49 rows=4849 width=67) (actual time=0.010..4.221 rows=4644 loops=1)"
" -> Hash (cost=1.55..1.55 rows=55 width=67) (actual time=0.067..0.067 rows=55 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 5kB"
" -> Seq Scan on station s2_ (cost=0.00..1.55 rows=55 width=67) (actual time=0.012..0.029 rows=55 loops=1)"
"Planning time: 10.644 ms"
"Execution time: 25.833 ms"
индекс по surname есть. вопрос - почему оптимизатор не выберет первые 50 записей и не пристегнет к ним остальное? нафига ворочать 4 тыши строк?
|
|
|
