Re: Partial index question

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Anton Nikiforov <anton(at)nikiforov(dot)ru>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Partial index question
Date: 2004-04-29 09:59:42
Message-ID: 20040429095942.GB26489@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Apr 29, 2004 at 12:53:24 +0400,
Anton Nikiforov <anton(at)nikiforov(dot)ru> wrote:
> Seq Scan on table_data (cost=0.00..4105.40 rows=63882 width=59)
> (actual time=0.477..425.550 rows=65536 loops=1)
> Filter: (game = 1)
> Total runtime: 657.153 ms
> It is no matter the type of select
> SELECT max(id) FROM table_data WHERE game=1;
> SELECT * FROM table_data WHERE game=1;
> The same selects even without where clause.
> I always have the same result - sequence scan but the filter appears in
> the explain output and the number of records being reduced.
> It that means that prtial index works? Or this is just means that i use
> where clause in the select?

You don't want to use an aggregate. For reasons why aggregates search the
archives. What you want is to have an index on (game, id) and
use:
SELECT id from table_data where game = 1 order by game desc, id desc limit 1;

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Paul Thomas 2004-04-29 10:11:58 Re: Partial index question
Previous Message Bruno Wolff III 2004-04-29 09:56:15 Re: Partial index question