From: Craig Ringer <craig@postnewspapers.com.au>
To: Chavdar Kopoev <chavdar@kopoev.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: many to many performance
Date: Thu, 27 Nov 2008 02:40:07 +0900
Message-ID: <492D89F7.6040700@postnewspapers.com.au> (raw)
In-Reply-To: <200811261738027964123@kopoev.com>
References: <200811261738027964123@kopoev.com>
Chavdar Kopoev wrote:
> I want to use as a data storage postgresql. Tried several data structures, testing btree, gin, gist indecies over them, but best achieved performance for a 10 times smaller dataset (10k cat ids, 100k doc ids, 1m relations) is slower more than 5 times.
Can you post your queries and table definitions so people trying to help
you know what you did / tried to do? A downloadable self contained
example might also be useful.
Please also post the output of `EXPLAIN' on your queries, eg:
EXPLAIN SELECT blah, ... FROM blah;
> I read about postgresql bitmap indecies and "data lookup" when scanning indecies to get a value for current transaction. Downloaded latest v8.4 snapshot, compiled it, but as I see there is no bitmap index in it. Maybe if I download HEAD revision I will find them there, dont know.
Bitmap index scans are an internal function that's used to combine two
indexes on the fly during a query (or at least use both of them in one
table scan). You don't make a bitmap index, you just make two ordinary
btree indexes and let Pg take care of this for you.
If you query on the columns of interest a lot, you might want to use a
multi-column index instead.
--
Craig Ringer
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: craig@postnewspapers.com.au, chavdar@kopoev.com
Subject: Re: many to many performance
In-Reply-To: <492D89F7.6040700@postnewspapers.com.au>
* 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