pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSearching union views not using indices
7+ messages / 5 participants
[nested] [flat]
* Searching union views not using indices
@ 2005-11-04 11:38 Michal Taborsky <michal.taborsky@mall.cz>
0 siblings, 3 replies; 7+ messages in thread
From: Michal Taborsky @ 2005-11-04 11:38 UTC (permalink / raw)
To: pgsql-performance
Hello everyone.
We are facing a performance problem with views consisting of several
unioned tables. The simplified schema is as follows:
CREATE TABLE foo (
foo_object_id bigint,
link_id bigint,
somedata text,
PRIMARY KEY (foo_object_id) );
CREATE TABLE bar (
bar_object_id bigint,
link_id bigint,
otherdata real,
PRIMARY KEY (bar_object_id) );
There are actually five of such tables, all having two common attributes
*_object_id and link_id. All tables have indices on link_id, which is
very selective, close to unique. The *_object_id is unique within this
scope across all tables, but that's not important.
Then we have a view:
CREATE VIEW commonview AS
SELECT foo_object_id as object_id, link_id, 'It is in foo' as loc
FROM foo
UNION
SELECT bar_object_id as object_id, link_id, 'It is in bar' as loc
FROM bar
We commonly do this:
SELECT object_id FROM commonview WHERE link_id=1234567
The result is sequential scan on all tables, append, sort and then
filter scan on this whole thing. Which of course is slow as hell. We use
version 8.0.2.
And now the question: Is there a way to force the planner to push the
condition lower, so it will use the index? Or do you use some tricks in
this scenario? Thanks for your suggestions.
Bye.
--
Michal Táborský
CTO, Internet Mall, a.s.
Internet Mall - obchody, které si oblíbíte
<http://www.MALL.cz;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 15:01 Richard Huxton <dev@archonet.com>
parent: Michal Taborsky <michal.taborsky@mall.cz>
2 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2005-11-04 15:01 UTC (permalink / raw)
To: Michal Taborsky <michal.taborsky@mall.cz>; +Cc: pgsql-performance
Michal Taborsky wrote:
...
> UNION
...
> The result is sequential scan on all tables, append, sort and then
> filter scan on this whole thing. Which of course is slow as hell. We use
> version 8.0.2.
>
> And now the question: Is there a way to force the planner to push the
> condition lower, so it will use the index? Or do you use some tricks in
> this scenario? Thanks for your suggestions.
Try "UNION ALL", since UNION is defined as removing duplicates, which
probably accounts for the sort.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 15:07 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2005-11-04 15:07 UTC (permalink / raw)
To: Michal Taborsky <michal.taborsky@mall.cz>; +Cc: pgsql-performance
> Hello everyone.
>
> We are facing a performance problem with views consisting of several
> unioned tables. The simplified schema is as follows:
>
> CREATE TABLE foo (
> foo_object_id bigint,
> link_id bigint,
> somedata text,
> PRIMARY KEY (foo_object_id) );
point 1:
well, you may want to consider:
create table foobar
(
prefix text, -- foo/bar/etc
object_id bigint,
link_id bigint,
primary key(prefix, object_id)
); -- add indexes as appropriate
and push foo/bar specific information to satellite table which refer
back via pkey-key link. Now you get very quick and easy link id query
and no view is necessary. You also may want to look at table
inheritance but make sure you read all the disclaimers first.
point 2:
watch out for union, it is implied sort and duplicate filter. union all
is faster although you may get duplicates.
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 15:12 Michael Fuhr <mike@fuhr.org>
parent: Michal Taborsky <michal.taborsky@mall.cz>
2 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-11-04 15:12 UTC (permalink / raw)
To: Michal Taborsky <michal.taborsky@mall.cz>; +Cc: pgsql-performance
On Fri, Nov 04, 2005 at 12:38:30PM +0100, Michal Taborsky wrote:
> SELECT object_id FROM commonview WHERE link_id=1234567
>
> The result is sequential scan on all tables, append, sort and then
> filter scan on this whole thing. Which of course is slow as hell. We use
> version 8.0.2.
I couldn't duplicate this in 8.0.4; I don't know if anything's
changed since 8.0.2 that would affect the query plan. Could you
post the EXPLAIN ANALYZE output? It might also be useful to see
the output with enable_seqscan disabled.
Have the tables been vacuumed and analyzed recently?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michal Taborsky <michal.taborsky@mall.cz>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2005-11-04 15:31 UTC (permalink / raw)
To: Michal Taborsky <michal.taborsky@mall.cz>; +Cc: pgsql-performance
Michal Taborsky <michal.taborsky@mall.cz> writes:
> We are facing a performance problem with views consisting of several
> unioned tables. The simplified schema is as follows:
Perhaps you should show us the real schema, because I cannot duplicate
your complaint on the toy case you show.
regression=# explain SELECT object_id FROM commonview WHERE link_id=1234567;
QUERY PLAN
------------------------------------------------------------------------------------------------
Subquery Scan commonview (cost=41.40..41.66 rows=13 width=8)
-> Unique (cost=41.40..41.53 rows=13 width=16)
-> Sort (cost=41.40..41.43 rows=13 width=16)
Sort Key: object_id, link_id, loc
-> Append (cost=0.00..41.16 rows=13 width=16)
-> Subquery Scan "*SELECT* 1" (cost=0.00..17.12 rows=5 width=16)
-> Index Scan using fooi on foo (cost=0.00..17.07 rows=5 width=16)
Index Cond: (link_id = 1234567)
-> Subquery Scan "*SELECT* 2" (cost=0.00..24.04 rows=8 width=16)
-> Index Scan using bari on bar (cost=0.00..23.96 rows=8 width=16)
Index Cond: (link_id = 1234567)
(11 rows)
(I had to add indexes on link_id to the example, of course.)
As noted by others, you probably want to be using UNION ALL not UNION,
but that's not the crux of the issue.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 15:55 Michal Taborsky <michal.taborsky@mall.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Michal Taborsky @ 2005-11-04 15:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane napsal(a):
> Michal Taborsky <michal.taborsky@mall.cz> writes:
>
>>We are facing a performance problem with views consisting of several
>>unioned tables. The simplified schema is as follows:
>
>
> Perhaps you should show us the real schema, because I cannot duplicate
> your complaint on the toy case you show.
> As noted by others, you probably want to be using UNION ALL not UNION,
> but that's not the crux of the issue.
OK. Mystery (sort of) solved. After you told me it works for you I had
to assume the problem was somewhere else. And, indeed, it was, though
it's not too obvious.
The two attributes are actually not of tybe bigint, but of type
"crm_object_id", which is created as follows:
CREATE DOMAIN "public"."crm_object_id" AS
bigint NULL;
Everything started working perfectly after I modified the view like this:
CREATE VIEW commonview AS
SELECT foo_object_id::bigint as object_id, link_id::bigint, 'It is in
foo' as loc FROM foo
UNION
SELECT bar_object_id::bigint as object_id, link_id::bigint, 'It is in
bar' as loc FROM bar
Not even modifying the select as this did not help:
explain SELECT object_id FROM commonview WHERE
link_id=1234567::crm_object_id;
Is this a bug or feature?
--
Michal Táborský
CTO, Internet Mall, a.s.
Internet Mall - obchody, které si oblíbíte
<http://www.MALL.cz;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Searching union views not using indices
@ 2005-11-04 17:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michal Taborsky <michal.taborsky@mall.cz>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-11-04 17:53 UTC (permalink / raw)
To: Michal Taborsky <michal.taborsky@mall.cz>; +Cc: pgsql-performance
Michal Taborsky <michal.taborsky@mall.cz> writes:
> OK. Mystery (sort of) solved. After you told me it works for you I had
> to assume the problem was somewhere else. And, indeed, it was, though
> it's not too obvious.
> The two attributes are actually not of tybe bigint, but of type
> "crm_object_id", which is created as follows:
> CREATE DOMAIN "public"."crm_object_id" AS
> bigint NULL;
Ah. The problem is that the UNION's output column is bigint, and the
type discrepancy (bigint above, domain below) discourages the planner
from pushing down the WHERE condition.
There's a related complaint here:
http://archives.postgresql.org/pgsql-bugs/2005-10/msg00227.php
If we were to change things so that the result of the UNION were still
the domain, not plain bigint, then your example would be optimized the
way you want. I'm unsure about what other side-effects that would have
though.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-11-04 17:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-04 11:38 Searching union views not using indices Michal Taborsky <michal.taborsky@mall.cz>
2005-11-04 15:01 ` Richard Huxton <dev@archonet.com>
2005-11-04 15:12 ` Michael Fuhr <mike@fuhr.org>
2005-11-04 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-04 15:55 ` Michal Taborsky <michal.taborsky@mall.cz>
2005-11-04 17:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-04 15:07 Re: Searching union views not using indices Merlin Moncure <merlin.moncure@rcsonline.com>
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