From: Justin Pryzby <pryzby@telsasoft.com>
To: Lev Kokotov <lev.kokotov@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Use Postgres as a column store by creating one table per column
Date: Tue, 21 May 2019 23:43:09 -0500
Message-ID: <20190522044309.GI4426@telsasoft.com> (raw)
In-Reply-To: <CAPwLVyJJ_ePn7O3PoMjSTJ64w=0RY2vGu4BfHRG58krC1Gkdcw@mail.gmail.com>
References: <CAPwLVyJJ_ePn7O3PoMjSTJ64w=0RY2vGu4BfHRG58krC1Gkdcw@mail.gmail.com>
On Tue, May 21, 2019 at 09:28:07PM -0700, Lev Kokotov wrote:
> Is it efficient to use Postgres as a column store by creating one table per
> column?
>
> I would query it with something like `[...] UNION SELECT value AS <table>
> FROM <table> WHERE value = <value> UNION [...]` to build a row.
I think you mean JOIN not UNION.
It'd be awful (At one point I tried it very briefly). If you were joining 2,
10 column tables, that'd be 19 joins. I imagine the tables would be "serial id
unique, float value" or similar, so the execution might not be terrible, as
it'd be using an index lookup for each column. But the planner would suffer,
badly. Don't even try to read EXPLAIN.
Actually, the execution would also be hitting at least 2x files per "column"
(one for the index and one for the table data), so that's not great.
Also, the overhead of a 2-column table is high, so your DB would be much bigger
and have very high overhead. Sorry to reference a 2ndary source, but..
https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes> I'm thinking since Postgres stores tables in continuous blocks of 16MB each
> (I think that's the default page size?) I would get efficient reads and
> with parallel queries I could benefit from multiple cores.
Default page size is 8kb
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, lev.kokotov@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Use Postgres as a column store by creating one table per column
In-Reply-To: <20190522044309.GI4426@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox