From: Hari Warrier <hbhaskaran@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Getting pg to use index on an inherited table (8.1.1)
Date: Tue, 17 Jan 2006 19:29:44 -0800
Message-ID: <43CDB628.3030007@gmail.com> (raw)
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.
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-performance@postgresql.org
Cc: hbhaskaran@gmail.com
Subject: Re: Getting pg to use index on an inherited table (8.1.1)
In-Reply-To: <43CDB628.3030007@gmail.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