postgresql: vertical partitioning brings no performance improvement -

postgresql: vertical partitioning brings no performance improvement -

i struggle optimizing performance of table vertical partitioning. next select statement should more optimized postgre imho:

select "productview".name, "productview".price, "productview".pid "defaultschema"."productview";

my schema looks this:

tables: producta(**pid**, name, price) productb(**pid**, desc) view: product(**pid**,name, price, desc)

the sql:

create table "defaultschema"."producta" ( pid integer not null, cost integer, name text, constraint pk_pa primary key (pid) ) create table "defaultschema"."productb" ( pid integer not null, "desc" text, constraint "pk_pb" primary key (pid), constraint "fk_pid" foreign key (pid) references "defaultschema"."producta" (pid) match simple on update cascade on delete cascade ) create or replace view "defaultschema"."productview" select p1.pid, p1.price, p1.name, p2."desc" "defaultschema"."producta" p1 bring together "defaultschema"."productb" p2 on p1.pid = p2.pid;

so might recognise not need productb select query. nevertheless joined during process of execution, can see here.

"hash bring together (cost=36.10..74.61 rows=1160 width=40) (actual time=0.090..0.105 rows=7 loops=1)" " hash cond: (p2.pid = p1.pid)" " -> seq scan on "productb" p2 (cost=0.00..22.30 rows=1230 width=4) (actual time=0.022..0.027 rows=7 loops=1)" " -> hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.030..0.030 rows=7 loops=1)" " buckets: 1024 batches: 1 memory usage: 1kb" " -> seq scan on "producta" p1 (cost=0.00..21.60 rows=1160 width=40) (actual time=0.010..0.017 rows=7 loops=1)" "total runtime: 0.299 ms"

my question how can forcefulness postgre scan producta? need addional constraint, edit config file or not possible gain performance advantage through vertical partitioning in postgre? lot in advance. :)

postgresql's query planner not yet bring together removal on inner joins.

you can either query "producta" alone, or rewrite view utilize left outer join. postgresql 9.0+ does bring together removal on left outer joins.

create or replace view "defaultschema"."productview" select p1.pid, p1.price, p1.name, p2."desc" "defaultschema"."producta" p1 left bring together "defaultschema"."productb" p2 on p1.pid = p2.pid; explain analyze select "productview".name, "productview".price, "productview".pid "productview"; query plan -- seq scan on "producta" p1 (cost=0.00..20.00 rows=1000 width=41) (actual time=0.008..0.225 rows=1000 loops=1)

rewriting utilize left outer bring together isn't safe in every application, think it's safe particular problem.

performance postgresql query-optimization vertical-partitioning