Optimierung einer einfachen Datenbank

Hallo,

ich habe folgende kleine Datenbank gegeben:

create table person (
  id serial primary key,
  name character varying not null,
  email character varying not null unique
);

create table person_alias (
  person integer references person(id),
  email character varying not null,
  primary key (person, email)
);

create table source_package (
  id serial primary key,
  name character varying not null unique,
  maintainer integer references person(id),
);

create table binary_package (
  id serial primary key,
  name character varying not null unique,
  source_package integer references source_package(id) unique
);

create table uploaders (
  person integer references person(id),
  source_package integer not null,
  primary key (person, source_package)
);

create view person_detail as
    select *,
        name || ' <' || email || '>' as fullname,
        (select count(*) from source_package where source_package.maintainer = person.id) as source_package_count,
        (select count(*) from binary_package
            join source_package on source_package.id = binary_package.source_package
            join person maintainer on maintainer.id = source_package.maintainer
            where person.id = maintainer.id) as binary_package_count
    from person;

In den einzelnen Tabellen befinden sich etwa so viele Eintraege:
person: 2000
uploaders: 8000
source_package: 10000
binary_package: 20000

Mein Problem besteht darin, dass ein relativ einfacher query auf den person_detail view eine extem hohe Last erzeugt und wirklich lange dauert (mehrere Minuten). Ein Beispiel query koennte so aussehen:

SELECT me.id, me.name, me.email, me.fullname, me.source_package_count, me.binary_package_count FROM person_detail me ORDER BY fullname LIMIT 10 OFFSET 590;

Das explain Kommando sagt folgendes zu selbigen query:

explain analyze SELECT me.id, me.name, me.email, me.fullname, me.source_package_count, me.binary_package_count FROM person_detail me ORDER BY fullname LIMIT 10 OFFSET 590;                                       
                                                                            QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1536216.65..1536216.68 rows=10 width=46) (actual time=362454.645..362454.778 rows=10 loops=1)
   ->  Sort  (cost=1536215.18..1536219.56 rows=1752 width=46) (actual time=362448.909..362451.836 rows=600 loops=1)
         Sort Key: ((((person.name)::text || ' <'::text) || (person.email)::text) || '>'::text)
         ->  Seq Scan on person  (cost=0.00..1536120.79 rows=1752 width=46) (actual time=302.040..362426.728 rows=1728 loops=1)
               SubPlan
                 ->  Aggregate  (cost=671.62..671.63 rows=1 width=0) (actual time=204.160..204.165 rows=1 loops=1728)
                       ->  Nested Loop  (cost=205.11..671.57 rows=20 width=0) (actual time=65.518..204.082 rows=12 loops=1728)
                             ->  Index Scan using person_pkey on person maintainer  (cost=0.00..3.01 rows=1 width=4) (actual time=0.020..0.027 rows=1 loops=1728)                   
                                   Index Cond: ($0 = id)
                             ->  Hash Join  (cost=205.11..668.35 rows=20 width=4) (actual time=65.473..203.906 rows=12 loops=1728)
                                   Hash Cond: ("outer".source_package = "inner".id)
                                   ->  Seq Scan on binary_package  (cost=0.00..359.36 rows=20736 width=4) (actual time=0.006..110.708 rows=20736 loops=1594)                        
                                   ->  Hash  (cost=205.09..205.09 rows=10 width=8) (actual time=5.436..5.436 rows=6 loops=1728)
                                         ->  Seq Scan on source_package  (cost=0.00..205.09 rows=10 width=8) (actual time=1.904..5.390 rows=6 loops=1728)                                       
                                               Filter: ($0 = maintainer)
                 ->  Aggregate  (cost=205.12..205.12 rows=1 width=0) (actual time=5.524..5.529 rows=1 loops=1728)
                       ->  Seq Scan on source_package  (cost=0.00..205.09 rows=10 width=0) (actual time=1.924..5.475 rows=6 loops=1728)
                             Filter: (maintainer = $0)
 Total runtime: 362455.268 ms
(19 rows)

Wie koennte man die Geschwindigkeit derartiger queries verbessern?


TIA,
Flo

Du könntest versuchen, auf “fullname” einen Index zu legen (siehe CREATE INDEX in der Doku). Aber ich bezweifle, ob das viel hilft. Im Prinzip muss die Datenbank die komplette Tabelle durchwühlen, um festzustellen, welche Reihenfolge die Einträge haben.

Du könntest auch einen Index auf binary_package.source_package und source_package.maintainer legen, vielleicht wird es dann etwas schneller. Ausserdem: lass mal testweise die ganzen count(*) weg und schau, ob sich die Geschwindigkeit dann verbessert.