From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Leif B. Kristensen <leif@solumslekt.org>
Cc: Pgsql-sql@postgresql.org <Pgsql-sql@postgresql.org>
Subject: Re: Use of partial index
Date: Wed, 5 Oct 2005 09:48:55 -0700 (PDT)
Message-ID: <20051005094704.F85645@megazone.bigpanda.com> (raw)
In-Reply-To: <200510051717.24390.leif@solumslekt.org>
References: <200510051717.24390.leif@solumslekt.org>
On Wed, 5 Oct 2005, Leif B. Kristensen wrote:
> I'm a little confused about partial indexes. I have a couple of tables,
> like this:
>
> CREATE TABLE events (
> event_id INTEGER PRIMARY KEY,
> tag_type_fk INTEGER REFERENCES tag_types (tag_type_id),
> place_fk INTEGER REFERENCES places (place_id),
> event_date CHAR(18) NOT NULL DEFAULT '000000003000000001',
> sort_date DATE NOT NULL DEFAULT '40041024BC',
> event_text TEXT NOT NULL DEFAULT '',
> sentence TEXT NOT NULL DEFAULT ''
> );
>
> To this table I have created a partial index:
>
> CREATE INDEX events_born
> ON events (tag_type_fk)
> WHERE tag_type_fk = 2;
>
> Another table:
>
> CREATE TABLE participants ( -- the TMG 'E' file
> participant_id INTEGER PRIMARY KEY,
> person_fk INTEGER REFERENCES persons (person_id),
> event_fk INTEGER REFERENCES events (event_id),
> role_type_fk INTEGER REFERENCES role_types (role_type_id),
> is_principal BOOLEAN NOT NULL DEFAULT 'f',
> is_primary_event BOOLEAN NOT NULL DEFAULT 'f',
> participant_note TEXT NOT NULL DEFAULT '',
> participant_name TEXT NOT NULL DEFAULT '',
> age_mean INTEGER NOT NULL DEFAULT 0,
> age_devi INTEGER NOT NULL DEFAULT 0,
> CONSTRAINT person_event UNIQUE (person_id, event_id)
> );
>
> And a view:
>
> CREATE OR REPLACE VIEW principals AS
> SELECT
> participants.person_fk AS person,
> events.event_id AS event,
> events.place_fk AS place,
> events.event_date AS event_date,
> events.sort_date AS sort_date,
> events.tag_type_fk AS tag_type
> FROM
> events, participants
> WHERE
> events.event_id = participants.event_fk
> AND
> participants.is_principal IS TRUE;
>
> Now, here's an "explain select":
>
> pgslekt=> explain select event_date, place from principals where
> person=2 and tag_type=2;
> QUERY PLAN
> -----------------------------------------------------------------------
> Nested Loop (cost=0.00..23.15 rows=2 width=26)
> -> Index Scan using person_event on participants
> (cost=0.00..13.63 rows=3 width=4)
> Index Cond: (person_fk = 2)
> Filter: (is_principal IS TRUE)
> -> Index Scan using events_pkey on events
> (cost=0.00..3.16 rows=1 width=30)
> Index Cond: (events.event_id = "outer".event_fk)
> Filter: (tag_type_fk = 2)
> (7 rader)
>
> Why doesn't this SELECT use the partial index "events_born" above? Is
> there any way to make this happen?
I would think you'd want an index ON events(event_id) WHERE tag_type_fk=2
for the query given.
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-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, leif@solumslekt.org
Subject: Re: Use of partial index
In-Reply-To: <20051005094704.F85645@megazone.bigpanda.com>
* 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