pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting pg to use index on an inherited table (8.1.1)
2+ messages / 2 participants
[nested] [flat]
* Getting pg to use index on an inherited table (8.1.1)
@ 2006-01-18 03:29 Hari Warrier <hbhaskaran@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Hari Warrier @ 2006-01-18 03:29 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have two tables foobar and foobar2 (which inherits from foobar, no
extra columns).
foobar2 has all the data (574,576 rows), foobar is empty.
Both foobar and foobar2 have an index on the only column 'id'. Now I
have a list of ids in a tmp_ids tables.
A query on foobar2 (child table) uses the index, whereas the same query
via foobar (parent) doesn't.
Even if I set seq_scan off, it still doesn't use the index on the child
table while queried via the parent table.
Details are given below. Any help is appreciated.
# analyze foobar;
ANALYZE
# analyze foobar2;
ANALYZE
# explain analyze select * from foobar2 join tmp_ids using (id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3013.69 rows=85856 width=4) (actual
time=0.038..234.864 rows=44097 loops=1)
-> Seq Scan on tmp_ids (cost=0.00..1.52 rows=52 width=4) (actual
time=0.008..0.102 rows=52 loops=1)
-> Index Scan using foobar2_idx1 on foobar2 (cost=0.00..37.29
rows=1651 width=4) (actual time=0.007..1.785 rows=848 loops=52)
Index Cond: (foobar2.id = "outer".id)
Total runtime: 302.963 ms
(5 rows)
# explain analyze select * from foobar join tmp_ids using (id);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.65..13267.85 rows=149946 width=4) (actual
time=7.338..3837.060 rows=44097 loops=1)
Hash Cond: ("outer".id = "inner".id)
-> Append (cost=0.00..8883.16 rows=576716 width=4) (actual
time=0.012..2797.555 rows=574576 loops=1)
-> Seq Scan on foobar (cost=0.00..31.40 rows=2140 width=4)
(actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on foobar2 foobar (cost=0.00..8851.76 rows=574576
width=4) (actual time=0.004..1027.422 rows=574576 loops=1)
-> Hash (cost=1.52..1.52 rows=52 width=4) (actual time=0.194..0.194
rows=52 loops=1)
-> Seq Scan on tmp_ids (cost=0.00..1.52 rows=52 width=4)
(actual time=0.003..0.094 rows=52 loops=1)
Total runtime: 3905.074 ms
(8 rows)
# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.3.3 (SuSE Linux)
(1 row)
# \d foobar
Table "public.foobar"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Indexes:
"foobar_idx1" btree (id)
# \d foobar2
Table "public.foobar2"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Indexes:
"foobar2_idx1" btree (id)
Inherits: foobar
# \d tmp_ids
Table "public.tmp_ids"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
# set enable_seqscan=off;
SET
# explain analyze select * from foobar join tmp_ids using (id);
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=200000001.65..300013267.85 rows=149946 width=4)
(actual time=7.352..3841.221 rows=44097 loops=1)
Hash Cond: ("outer".id = "inner".id)
-> Append (cost=100000000.00..200008883.16 rows=576716 width=4)
(actual time=0.012..2803.547 rows=574576 loops=1)
-> Seq Scan on foobar (cost=100000000.00..100000031.40
rows=2140 width=4) (actual time=0.003..0.003 rows=0 loops=1)
-> Seq Scan on foobar2 foobar
(cost=100000000.00..100008851.76 rows=574576 width=4) (actual
time=0.005..1032.148 rows=574576 loops=1)
-> Hash (cost=100000001.52..100000001.52 rows=52 width=4) (actual
time=0.194..0.194 rows=52 loops=1)
-> Seq Scan on tmp_ids (cost=100000000.00..100000001.52
rows=52 width=4) (actual time=0.004..0.098 rows=52 loops=1)
Total runtime: 3909.332 ms
(8 rows)
Output of "show all" (remember I just turned off seq_scan above)
enable_bitmapscan |
on | Enables the planner's use of
bitmap-scan plans.
enable_hashagg |
on | Enables the planner's use of
hashed aggregation plans.
enable_hashjoin |
on | Enables the planner's use of
hash join plans.
enable_indexscan |
on | Enables the planner's use of
index-scan plans.
enable_mergejoin |
on | Enables the planner's use of
merge join plans.
enable_nestloop |
on | Enables the planner's use of
nested-loop join plans.
enable_seqscan |
off | Enables the planner's use of
sequential-scan plans.
enable_sort |
on | Enables the planner's use of
explicit sort steps.
enable_tidscan |
on | Enables the planner's use of
TID scan plans.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting pg to use index on an inherited table (8.1.1)
@ 2006-01-18 04:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hari Warrier <hbhaskaran@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-18 04:07 UTC (permalink / raw)
To: Hari Warrier <hbhaskaran@gmail.com>; +Cc: pgsql-performance
Hari Warrier <hbhaskaran@gmail.com> writes:
> A query on foobar2 (child table) uses the index, whereas the same query
> via foobar (parent) doesn't.
A query just on foobar should be able to use the index AFAIR. The
problem here is that you have a join, and we are not very good about
situations involving joins against inheritance sets (nor joins against
UNION ALL subqueries, which is really about the same thing).
I'm hoping to get a chance to look into improving this during the 8.2
development cycle.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-18 04:07 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-18 03:29 Getting pg to use index on an inherited table (8.1.1) Hari Warrier <hbhaskaran@gmail.com>
2006-01-18 04:07 ` Tom Lane <tgl@sss.pgh.pa.us>
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