From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: indexing for distinct search in timestamp based table
Date: Fri, 5 Sep 2008 19:10:35 +0400
Message-ID: <1d709ecc0809050810m42bfbedfk98ac7d83f13fb7@mail.gmail.com> (raw)
In-Reply-To: <000c01c908e4$f64e92c0$e2ebb840$@com>
References: <000c01c908e4$f64e92c0$e2ebb840$@com>
You might get great improvement for '%' cases using index on
channel_name(<field>,
start_time) and a little bit of pl/pgsql
Basically, you need to implement the following algorithm:
1) curr_<field> = ( select min(<field>) from ad_log )
2) record_exists = ( select 1 from ad_log where <field>=cur_<field> and
_all_other_conditions limit 1 )
3) if record_exists==1 then add curr_<field> to the results
3) curr_<field> = (select min(<field>) from ad_log where <field> >
curr_<field> )
4) if curr_<field> is not null then goto 2
I believe it might make sense implement this approach in the core (I would
call it "index distinct scan")
That could dramatically improve "select distinct <column> from <table>" and
"select <column> from <table> group by <column>" kind of queries when there
exists an index on <column> and a particular column has very small number of
distinct values.
For instance: say a table has 10'000'000 rows, while column of interest has
only 20 distinct values. In that case, the database will be able to get
every of those 20 values in virtually 20 index lookups.
What does the community think about that?
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: sitnikov.vladimir@gmail.com
Subject: Re: indexing for distinct search in timestamp based table
In-Reply-To: <1d709ecc0809050810m42bfbedfk98ac7d83f13fb7@mail.gmail.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