To: Mark Lewis <mark.lewis@mir3.com>
cc: gdavis@refractions.net, Chris Browne <cbbrowne@acm.org>,
	pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index 
In-reply-to: <1159912457.18640.96.camel@archimedes> 
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
	<20061003000147.GA31475@wolff.to>
	<4522AA01.4020709@refractions.net>
	<60irj1gr3r.fsf@dba2.int.libertyrms.com>
	<4522B5B9.2050206@refractions.net>
	<60d599gqcz.fsf@dba2.int.libertyrms.com>
	<4522C8D8.4010601@refractions.net> <10941.1159908489@sss.pgh.pa.us>
	<4522CD8C.1000108@refractions.net>
	<1159909612.18640.80.camel@archimedes>
	<4522D4CE.4070806@refractions.net>
	<1159911267.18640.83.camel@archimedes>
	<4522D7B1.3040008@refractions.net>
	<1159912457.18640.96.camel@archimedes>
Comments: In-reply-to Mark Lewis <mark.lewis@mir3.com>
	message dated "Tue, 03 Oct 2006 14:54:17 -0700"
Date: Tue, 03 Oct 2006 18:39:32 -0400
Message-ID: <25227.1159915172@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Lewis <mark.lewis@mir3.com> writes:
> Have you considered creating one partial index per assetid?  Something
> along the lines of "CREATE INDEX asset_index_N ON asset_positions(ts)
> WHERE assetid=N"?  I'd guess that the planner probably wouldn't be smart
> enough to use the partial indexes unless you issued a separate query for
> each assetid, but each one of those queries should be really fast.

Actually, a single index on (assetid, ts) is sufficient to handle

	select max(ts) from asset_positions where assetid = constant

The problem is to know what values of "constant" to issue the query for,
and this idea doesn't seem to help with that.

If Graham is willing to assume that the set of assetids changes slowly,
perhaps he could keep a summary table that contains all the valid
assetids (or maybe there already is such a table?  is assetid a foreign
key?) and do

	select pk.assetid,
               (select max(ts) from asset_positions where assetid = pk.assetid)
	from other_table pk;

I'm pretty sure the subselect would be planned the way he wants.

			regards, tom lane