pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUse of partial index
6+ messages / 4 participants
[nested] [flat]
* Use of partial index
@ 2005-10-05 15:17 Leif B. Kristensen <leif@solumslekt.org>
2005-10-05 16:48 ` Re: Use of partial index Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-10-05 16:49 ` Re: Use of partial index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Leif B. Kristensen @ 2005-10-05 15:17 UTC (permalink / raw)
To: pgsql-sql
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?
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use of partial index
2005-10-05 15:17 Use of partial index Leif B. Kristensen <leif@solumslekt.org>
@ 2005-10-05 16:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2005-10-05 16:48 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use of partial index
2005-10-05 15:17 Use of partial index Leif B. Kristensen <leif@solumslekt.org>
@ 2005-10-05 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-05 17:20 ` Re: Use of partial index Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-10-05 16:49 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> 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;
> ...
> 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?
Because the plan it did choose is better. events_born could only serve
to select the rows with tag_type_fk = 2; assuming there's more than one
of those rows, there would be multiple fetches needed to see if any of
them have the desired event_id. With this plan it's getting at most one
row, by definition (since event_id is the primary key).
Had you created the partial index as
CREATE INDEX events_born
ON events (event_id)
WHERE tag_type_fk = 2;
then it would be competitive for this query, since the index could
effectively handle both constraints not just one. (THe way you did
define it, the actual content of the index keys is just dead weight,
since they obviously must all be "2". It's often better to define
the index column(s) of a partial index as some other column than the
one involved in the index predicate...)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use of partial index
2005-10-05 15:17 Use of partial index Leif B. Kristensen <leif@solumslekt.org>
2005-10-05 16:49 ` Re: Use of partial index Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-05 17:20 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 6+ messages in thread
From: Leif B. Kristensen @ 2005-10-05 17:20 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 05 October 2005 18:49, you wrote:
[Leif]
> > 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)
>
> Because the plan it did choose is better. events_born could only
> serve to select the rows with tag_type_fk = 2; assuming there's more
> than one of those rows, there would be multiple fetches needed to see
> if any of them have the desired event_id. With this plan it's getting
> at most one row, by definition (since event_id is the primary key).
>
> Had you created the partial index as
>
> CREATE INDEX events_born
> ON events (event_id)
> WHERE tag_type_fk = 2;
>
> then it would be competitive for this query, since the index could
> effectively handle both constraints not just one. (THe way you did
> define it, the actual content of the index keys is just dead weight,
> since they obviously must all be "2". It's often better to define
> the index column(s) of a partial index as some other column than the
> one involved in the index predicate...)
>
pgslekt=> create index events_born2 on events (event_id) where
tag_type_fk=2;
CREATE INDEX
pgslekt=> explain select event_date, place from principals where
person=2 and tag_type=2;
QUERY PLAN
----------------------------------------------------------------------------------------
Nested Loop (cost=0.00..22.88 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_born2 on events (cost=0.00..3.07 rows=1
width=30)
Index Cond: (events.event_id = "outer".event_fk)
Filter: (tag_type_fk = 2)
(7 rader)
From 23.15 to 22.88 ... but now at least it used my partial index, as it
does a slightly better job. I'm starting to get it - I think.
Thank you for your explanation. With regards to optimization, it seems
that I'm still too hung up in MySQL issues. PostgreSQL seems to behave
a lot more intelligently with queries.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use of partial index
@ 2005-10-05 16:44 Dmitri Bichko <dbichko@aveopharma.com>
2005-10-05 17:21 ` Re: Use of partial index Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 6+ messages in thread
From: Dmitri Bichko @ 2005-10-05 16:44 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
As I understand it, partial indices are generally useful when you only
want to index a range of values, or if the select condition is on a
different field from the one being indexed (eg: ON foo (a) WHERE b IS
NOT NULL).
I am just guessing here, but it sounds like 'person_fk = 2' is going to
be a lot more selective (ie return less rows) than 'tag_type_fk = 2', so
it's quicker to use the pkey and then filter the results.
Depending on how many 'tag_type' values you have, indexing on it will
not help at all. In other words, if more than a few percent of the rows
have the value '2' for 'tag_type_fg', postgres will tend to favour more
selective indices if you are doing a join, or a seqscan if you are doing
a straight select on that value.
Does that help?
Dmitri
PS Your query seems to be quite quick already, why don't you like this
plan?
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Leif B.
> Kristensen
> Sent: Wednesday, October 05, 2005 11:17 AM
> To: Pgsql-sql@postgresql.org
> Subject: [SQL] Use of partial index
>
>
> 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?
> --
> Leif Biberg Kristensen
> http://solumslekt.org/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use of partial index
2005-10-05 16:44 Re: Use of partial index Dmitri Bichko <dbichko@aveopharma.com>
@ 2005-10-05 17:21 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 6+ messages in thread
From: Leif B. Kristensen @ 2005-10-05 17:21 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 05 October 2005 18:44, you wrote:
> As I understand it, partial indices are generally useful when you
> only want to index a range of values, or if the select condition is
> on a different field from the one being indexed (eg: ON foo (a) WHERE
> b IS NOT NULL).
>
> I am just guessing here, but it sounds like 'person_fk = 2' is going
> to be a lot more selective (ie return less rows) than 'tag_type_fk =
> 2', so it's quicker to use the pkey and then filter the results.
That makes a lot of sense to me. As any person will usually participate
in several events, I'll estimate that the ratio between person_fk=x and
tag_type_fk=y is about 1:4.
> Depending on how many 'tag_type' values you have, indexing on it will
> not help at all. In other words, if more than a few percent of the
> rows have the value '2' for 'tag_type_fg', postgres will tend to
> favour more selective indices if you are doing a join, or a seqscan
> if you are doing a straight select on that value.
I have only 53 different tag types, but most of them are rather rare.
The most heavilyly used are birth=2, death=3, and marriage=4, and I've
created similar partial indexes for the other two. In a previous MySQL
project that's roughly sharing the same data model, I created a
redundant table called "marriages" that speeded up the generation of a
family view by a factor of four. That's why I thought that a partial
index would have a similar effect here.
> Does that help?
Yessir, thank you very much!
> Dmitri
> PS Your query seems to be quite quick already, why don't you like
> this plan?
I didn't say that I didn't like it, - I just was a little perplexed :-)
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-05 17:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-05 15:17 Use of partial index Leif B. Kristensen <leif@solumslekt.org>
2005-10-05 16:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-10-05 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-05 17:20 ` Leif B. Kristensen <leif@solumslekt.org>
2005-10-05 16:44 Re: Use of partial index Dmitri Bichko <dbichko@aveopharma.com>
2005-10-05 17:21 ` Leif B. Kristensen <leif@solumslekt.org>
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