pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Lewis <mark.lewis@mir3.com>
Cc: gdavis@refractions.net, Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index
Date: Tue, 03 Oct 2006 18:39:32 -0400
Message-ID: <25227.1159915172@sss.pgh.pa.us> (raw)
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>

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



view thread (17+ messages)

Message-ID: <25227.1159915172@sss.pgh.pa.us>
Permalink:  ../25227.1159915172@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25227.1159915172@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, mark.lewis@mir3.com, cbbrowne@acm.org
  Subject: Re: BUG #2658: Query not using index
  In-Reply-To: <25227.1159915172@sss.pgh.pa.us>

* 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