Skip site navigation (1) Skip section navigation (2)

Re: many to many performance

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Chavdar Kopoev <chavdar(at)kopoev(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: many to many performance
Date: 2008-11-26 17:40:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: AndrusDate: 2008-11-26 18:56:28
Subject: Re: Increasing pattern index query speed
Previous:From: Chavdar KopoevDate: 2008-11-26 15:38:07
Subject: many to many performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group