Re: Partial index question

From: Anton Nikiforov <anton(at)nikiforov(dot)ru>
To: anton(at)nikiforov(dot)ru
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Partial index question
Date: 2004-04-29 08:53:24
Message-ID: 4090C284.3060605@nikiforov.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Anton Nikiforov пишет:

> ....skipped.....
> CREATE UNIQUE INDEX type_index ON table_data (type, id);
> or
> CREATE UNIQUE INDEX type_1_index ON table_data (id) WHERE type=1;
> CREATE UNIQUE INDEX type_X_index ON table_data (id) WHERE type=X;
>
Hello All,
looks like after a few tests that i've done i'm confused more than before.
I did create a table and inserted 200000 random records of two different
types of data into it.
Now i'm trying to do different selects with different where options.
And i'm always getting explain analyze output like this:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
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?

--
Best regads,
Anton Nikiforov

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2004-04-29 08:53:42 Re: Partial index question
Previous Message Tatsuo Ishii 2004-04-29 08:44:51 Re: Postgre and Web Request