@artur_bredzki wynik testu, który przeprowadziłeś sugeruje niewłaściwe indeksowanie.
Dlatego piszę, że ja nie umiem lepiej zaindeksować, a nie że w ogóle nie da się lepiej. Na razie nikt mi nie
pokazał, że można lepiej zaindeksować.
Czy możesz wrzucić CREATE
tabel users
i countries
?
Tak, cały skrypt jest ten sam, wraz z kodem generującym przykładowe dane, 300 krajów, 3mln użytkowników, wystarczy odpalić:
\timing off
\pset pager off
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
\c postgres;
DROP DATABASE IF EXISTS test1;
DROP ROLE IF EXISTS test1;
CREATE ROLE test1 PASSWORD 'test1' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test1 OWNER test1 ENCODING 'UTF-8';
\c test1;
set role test1;
CREATE TABLE countries (
id serial NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE users (
id serial NOT NULL,
id_country integer NOT NULL,
name character varying(10) NOT NULL
);
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{A,B,C,D,E,F,G,H}';
result text := '';
i integer := 0;
begin
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$ language plpgsql;
create or replace function mk_countries(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into countries (name) values( random_string(5) )' ;
end loop;
end;
$$ language plpgsql;
create or replace function mk_users(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into users (id_country,name) values( 1+random()*299 , random_string(4) )';
end loop;
end;
$$ language plpgsql;
begin;
select mk_countries(300);
select mk_users(3000000);
commit;
ALTER TABLE ONLY countries ADD CONSTRAINT countries_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_id_country_fkey FOREIGN KEY (id_country) REFERENCES countries(id);
CREATE INDEX countries_name ON countries(name);
CREATE INDEX users_name ON users(name);
CREATE INDEX users_id_country ON users(id_country);
SELECT i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass,
am.amname as indam,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) as indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred
FROM pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
JOIN pg_am as am
ON i.relam = am.oid
JOIN pg_namespace as ns
ON ns.oid = i.relnamespace
AND ns.nspname = ANY(current_schemas(false))
ORDER BY i.relname;
\timing on
--przykłady zapytań
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name from users as u order by u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
Zwróć uwagę na to, że sortujesz wynik zapytania po nazwie kraju. Widać, że indeks na tę kolumnę założyłeś w przykładzie z ENUM
(create index users_cn on users(country,name);
), ale czy taki indeks istniał w tabeli słownikowej? Ponadto czy w tabeli users
był założony indeks na country_id
?
Powiem tak, dużo kombinowalem, nie udalo się. Krajów jest 300, moim zdaniem indeks zadziała od np. 50tys rekordów - w zalezności od innych szczegółów.
Tak duży spadek wydajności raczej nie jest spowodowany JOINem
do słownika, a niewłaściwym indeksowaniem obu tabel.
Może masz rację, nie mówię że nie, tym bardziej że ekspertem nie jestem. Ja tylko chciałbym to zobaczyć na działającym przykładzie.
Co do ALTERa
- linki, które podałeś traktują o Postgresie, a nie o MySQL.
Tak, ale w mysqlu powinny być odpowiedniki.
Pozdrawiam