From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: Digesting explain analyze
Date: Wed, 06 Jan 2010 12:03:21 -0800
Message-ID: <4B44EC89.7060200@cheapcomplexdevices.com> (raw)
In-Reply-To: <4B44E02A.6080109@krogh.cc>
References: <4B44E02A.6080109@krogh.cc>
Jesper Krogh wrote:
> I have a table that consists of somewhere in the magnitude of 100.000.000
> rows and all rows are of this tuples
>
> (id1,id2,evalue);
>
> Then I'd like to speed up a query like this:
>
> explain analyze select id from table where id1 = 2067 or id2 = 2067 order
> by evalue asc limit 100;
>
> ...The inner sets are on average 3.000 for
> both id1 and id2 and a typical limit would be 100, so if I could convince
> postgresql to not fetch all of them then I would reduce the set retrieved
> by around 60. The dataset is quite large so the random query is not very
> likely to be hitting the same part of the dataset again, so there is going
> to be a fair amount of going to disk.,
If disk seeks are killing you a kinda crazy idea would be to
duplicate the table - clustering one by (id1) and
the other one by an index on (id2) and unioning the
results of each.
Since each of these duplicates of the table will be clustered
by the column you're querying it on, it should just take one
seek in each table.
Then your query could be something like
select * from (
select * from t1 where id1=2067 order by evalue limit 100
union
select * from t2 where id2=2067 order by evalue limit 100
) as foo order by evalue limit 100;
Hmm.. and I wonder if putting evalue into the criteria to cluster
the tables too (i.e. cluster on id1,evalue) if you could make it
so the limit finds the right 100 evalues first for each table....
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: rm_pg@cheapcomplexdevices.com, jesper@krogh.cc
Subject: Re: Digesting explain analyze
In-Reply-To: <4B44EC89.7060200@cheapcomplexdevices.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