Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

Next:From: Paul ThomasDate: 2004-04-29 10:11:58
Subject: Re: Partial index question
Previous:From: Bruno Wolff IIIDate: 2004-04-29 09:56:15
Subject: Re: Partial index question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group