Hi all,
I'm using Postgresql 8.4.4 on Debian.
In postgresql.conf, constraint_exclusion is set to "on"
I have partitioned tables with check constraints.
My primary table :
CREATE TABLE documents
(
id serial NOT NULL,
id_source smallint,
nod integer,
num text,
id_fourniture integer,
dav date NOT NULL,
maj timestamp without time zone NOT NULL DEFAULT now(),
id_location "char",
id_partition smallint,
mark text
);
There is no row in "only" documents :
SQL> select count(*) from only documents;
-> 0
SQL> select count(*) from documents;
-> 160155756
I have one thousand inherited tables like this one (with a different
check constraint on each) :
CREATE TABLE documents_mond
(
CONSTRAINT documents_mond_id_source_check CHECK (id_source = 113)
)
INHERITS (documents);
CREATE INDEX idx_documents_mond_id
ON documents_mond
USING btree
(id);
CREATE INDEX idx_documents_mond_id_partition
ON documents_mond
USING btree
(id_partition);
CREATE INDEX idx_documents_mond_id_source_dav
ON documents_mond
USING btree
(id_source, dav);
ALTER TABLE documents_mond CLUSTER ON
idx_documents_mond_id_source_dav;
CREATE INDEX idx_documents_mond_id_source_nod
ON documents_mond
USING btree
(id_source, nod);
CREATE INDEX idx_documents_mond_id_source_num
ON documents_mond
USING btree
(id_source, num);
CREATE INDEX idx_documents_mond_maj
ON documents_mond
USING btree
(maj);
SQL> select count(*) from documents_mond;
-> 1053929
When i perform this query on the primary table :
EXPLAIN ANALYZE
select
documents.id,
documents.num,
sources.name,
l.name
from
documents,
locations l,
sources
where
documents.id_source = 113 and
documents.id_location=l.id and
documents.id_source=sources.id
order by
documents.id desc
limit 5;
"Limit (cost=36209.55..36209.57 rows=5 width=24) (actual
time=2307.181..2307.185 rows=5 loops=1)"
" -> Sort (cost=36209.55..36512.56 rows=121202 width=24) (actual
time=2307.180..2307.180 rows=5 loops=1)"
" Sort Key: public.documents.id"
" Sort Method: top-N heapsort Memory: 17kB"
" -> Nested Loop (cost=1.52..34196.43 rows=121202 width=24)
(actual time=0.076..1878.189 rows=1053929 loops=1)"
" -> Index Scan using pk_sources on sources
(cost=0.00..8.27 rows=1 width=8) (actual time=0.013..0.015 rows=1
loops=1)"
" Index Cond: (id = 113)"
" -> Hash Join (cost=1.52..32976.15 rows=121202
width=22) (actual time=0.059..1468.982 rows=1053929 loops=1)"
" Hash Cond: (public.documents.id_location = l.id)"
" -> Append (cost=0.00..27810.36 rows=1053932
width=14) (actual time=0.031..836.280 rows=1053929 loops=1)"
" -> Seq Scan on documents
(cost=0.00..18.25 rows=3 width=39) (actual time=0.001..0.001 rows=0
loops=1)"
" Filter: (id_source = 113)"
" -> Seq Scan on documents_mond documents
(cost=0.00..27792.11 rows=1053929 width=14) (actual
time=0.030..503.815 rows=1053929 loops=1)"
" Filter: (id_source = 113)"
" -> Hash (cost=1.23..1.23 rows=23 width=10)
(actual time=0.019..0.019 rows=23 loops=1)"
" -> Seq Scan on locations l
(cost=0.00..1.23 rows=23 width=10) (actual time=0.001..0.007 rows=23
loops=1)"
"Total runtime: 2307.498 ms"
And when i perform the same query directly on the inherited table
(CHECK id_source=113) :
EXPLAIN ANALYZE
select
documents.id,
documents.num,
sources.name,
l.name
from
documents_mond documents,
locations l,
sources
where
documents.id_source = 113 and
documents.id_location=l.id and
documents.id_source=sources.id
order by
documents.id desc
limit 5;
"Limit (cost=0.00..43.13 rows=5 width=24) (actual time=0.024..0.050
rows=5 loops=1)"
" -> Nested Loop (cost=0.00..9091234.75 rows=1053929 width=24)
(actual time=0.023..0.049 rows=5 loops=1)"
" -> Nested Loop (cost=0.00..8796038.31 rows=1053929
width=16) (actual time=0.020..0.035 rows=5 loops=1)"
" -> Index Scan Backward using idx_documents_mond_id on
documents_mond documents (cost=0.00..71930.23 rows=1053929 width=14)
(actual time=0.012..0.015 rows=5 loops=1)"
" Filter: (id_source = 113)"
" -> Index Scan using pk_sources on sources
(cost=0.00..8.27 rows=1 width=8) (actual time=0.003..0.003 rows=1
loops=5)"
" Index Cond: (sources.id = 113)"
" -> Index Scan using locations_pkey on locations l
(cost=0.00..0.27 rows=1 width=10) (actual time=0.001..0.002 rows=1
loops=5)"
" Index Cond: (l.id = documents.id_location)"
"Total runtime: 0.086 ms"
OR
EXPLAIN ANALYZE
select
documents.id,
documents.num,
sources.name,
l.name
from
documents_mond documents,
locations l,
sources
where
/* documents.id_source = 113 and */
documents.id_location=l.id and
documents.id_source=sources.id
order by
documents.id desc
limit 5;
"Limit (cost=0.00..3.13 rows=5 width=24) (actual time=0.025..0.052
rows=5 loops=1)"
" -> Nested Loop (cost=0.00..659850.75 rows=1053929 width=24)
(actual time=0.024..0.051 rows=5 loops=1)"
" -> Nested Loop (cost=0.00..364654.31 rows=1053929 width=16)
(actual time=0.021..0.037 rows=5 loops=1)"
" -> Index Scan Backward using idx_documents_mond_id on
documents_mond documents (cost=0.00..69295.41 rows=1053929 width=14)
(actual time=0.011..0.013 rows=5 loops=1)"
" -> Index Scan using pk_sources on sources
(cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.003 rows=1
loops=5)"
" Index Cond: (sources.id = documents.id_source)"
" -> Index Scan using locations_pkey on locations l
(cost=0.00..0.27 rows=1 width=10) (actual time=0.002..0.002 rows=1
loops=5)"
" Index Cond: (l.id = documents.id_location)"
"Total runtime: 0.091 ms"
Is it a normal behavior ?
I need to rewrite all my Perl scripts to have query pointing only on
inherited tables (when possible) ?
I was thinking that query pointing on primary table were correctly
dispatched on inherited tables ... I missing something ?
Regards
Philippe
Ps : I'm french, so my english is approximate ... hoping it's
understandable
