pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not used on join with inherited tables
3+ messages / 2 participants
[nested] [flat]
* Index not used on join with inherited tables
@ 2005-05-30 15:54 Sebastian Böck <sebastianboeck@freenet.de>
2005-05-30 16:21 ` Re: Index not used on join with inherited tables Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sebastian Böck @ 2005-05-30 15:54 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I'm having another problem with a query that takes to long, because
the appropriate index is not used.
I found some solutions to this problem, but I think Postgres should do
an index scan in all cases.
To show the problem I've attached a small script with a testcase.
Thanks in advance
Sebastian
Attachments:
bug.sql (text/plain, 1.3K) download
/* tables */
CREATE TABLE test (
id SERIAL PRIMARY KEY,
test TEXT
);
CREATE TABLE test1 (
id INTEGER PRIMARY KEY
) INHERITS (test);
CREATE TABLE test2 (
id INTEGER PRIMARY KEY
) INHERITS (test);
CREATE TABLE test3 (
id INTEGER PRIMARY KEY
) INHERITS (test);
CREATE TABLE bug (
id INTEGER PRIMARY KEY
);
/* views */
CREATE OR REPLACE VIEW working_fast AS
SELECT *
FROM test
WHERE id = 1;
CREATE OR REPLACE VIEW working_fast_2 AS
SELECT test1.* FROM test1 JOIN bug AS bug1 ON TRUE WHERE test1.id = bug1.id
UNION ALL
SELECT test2.* FROM test2 JOIN bug AS bug2 ON TRUE WHERE test2.id = bug2.id
UNION ALL
SELECT test3.* FROM test3 JOIN bug AS bug3 ON TRUE WHERE test3.id = bug3.id;
CREATE OR REPLACE VIEW working_slow AS
SELECT test.*
FROM test
JOIN bug ON TRUE
WHERE test.id = bug.id;
/* data */
CREATE OR REPLACE FUNCTION data () RETURNS BOOLEAN AS $$
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO test1 (id,test) VALUES (DEFAULT,i);
INSERT INTO test2 (id,test) VALUES (DEFAULT,i);
INSERT INTO test3 (id,test) VALUES (DEFAULT,i);
END LOOP;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
SELECT data();
INSERT INTO bug VALUES ('1');
ANALYZE;
EXPLAIN ANALYZE SELECT * from working_fast;
EXPLAIN ANALYZE SELECT * from working_fast_2;
EXPLAIN ANALYZE SELECT * from working_slow;
* Re: Index not used on join with inherited tables
2005-05-30 15:54 Index not used on join with inherited tables Sebastian Böck <sebastianboeck@freenet.de>
@ 2005-05-30 16:21 ` Josh Berkus <josh@agliodbs.com>
2005-05-30 16:36 ` Re: Index not used on join with inherited tables Sebastian Böck <sebastianboeck@freenet.de>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2005-05-30 16:21 UTC (permalink / raw)
To: Sebastian Böck <sebastianboeck@freenet.de>; +Cc: pgsql-performance
Sebastian,
> I'm having another problem with a query that takes to long, because
> the appropriate index is not used.
PostgreSQL is not currently able to push down join criteria into UNIONed
subselects. It's a TODO.
Also, if you're using inherited tables, it's unnecessary to use UNION; just
select from the parent.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not used on join with inherited tables
2005-05-30 15:54 Index not used on join with inherited tables Sebastian Böck <sebastianboeck@freenet.de>
2005-05-30 16:21 ` Re: Index not used on join with inherited tables Josh Berkus <josh@agliodbs.com>
@ 2005-05-30 16:36 ` Sebastian Böck <sebastianboeck@freenet.de>
0 siblings, 0 replies; 3+ messages in thread
From: Sebastian Böck @ 2005-05-30 16:36 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus wrote:
> Sebastian,
>
>
>>I'm having another problem with a query that takes to long, because
>>the appropriate index is not used.
>
>
> PostgreSQL is not currently able to push down join criteria into UNIONed
> subselects. It's a TODO.
And the appends in a "SELECT * from parent" are UNIONs, aren't they?
> Also, if you're using inherited tables, it's unnecessary to use UNION; just
> select from the parent.
Yes, but then no index is used...
Sebastian
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-30 16:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-30 15:54 Index not used on join with inherited tables Sebastian Böck <sebastianboeck@freenet.de>
2005-05-30 16:21 ` Josh Berkus <josh@agliodbs.com>
2005-05-30 16:36 ` Sebastian Böck <sebastianboeck@freenet.de>
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