I appear to have been able to replicate what you are talking about, but
it required explicitly binding the order by in different ways. See
attached files.
William King
Senior Engineer
Quentus Technologies, INC
1037 NE 65th St Suite 273
Seattle, WA 98115
Main: (877) 211-9337
Office: (206) 388-4772
Cell: (253) 686-5518
william.k...@quentustech.com
On 05/26/2013 02:22 AM, Stefan Keller wrote:
> Yes, it actually does, but the planner chooses a seq scan to prepare for that.
>
> -S.
>
> 2013/5/26 William King <william.k...@quentustech.com>:
>> Could this scenario not be handled by a step that orders the two tables
>> independently, then for the view interleaves the presorted results?
>> Merging two sorted sets into a single sorted set is usually a trivial
>> task, and it could still take advantage of the existing indexes.
>>
>> William King
>> Senior Engineer
>> Quentus Technologies, INC
>> 1037 NE 65th St Suite 273
>> Seattle, WA 98115
>> Main: (877) 211-9337
>> Office: (206) 388-4772
>> Cell: (253) 686-5518
>> william.k...@quentustech.com
>>
>> On 05/25/2013 05:35 PM, Stefan Keller wrote:
>>> Hi
>>>
>>> I've encountered a fundamental problem which - to me - can only be
>>> solved with an (future/possible) real index on views in PostgreSQL
>>> (like the exist already in MS SQL Server and Ora):
>>>
>>> Given following schema:
>>>
>>> 1. TABLE a and TABLE b, each with INDEX on attribute geom.
>>>
>>> 2. A VIEW with union:
>>>
>>> CREATE VIEW myview AS
>>> SELECT * FROM a
>>> UNION
>>> SELECT * FROM b;
>>>
>>> 3. And a simple query with KNN index and a coordinate "mypos" :
>>>
>>> SELECT * FROM myview
>>> ORDER BY ST_Geomfromtext(mypos) <-> myview.geom
>>>
>>> Now, the problem is, that for the "order by" it is not enough that
>>> each on the two tables calculate the ordering separately: We want a
>>> total ordering over all involved tables!
>>>
>>> In fact, the planner realizes that and chooses a seq scan over all
>>> tuples of table a and b - which is slow and suboptimal!
>>>
>>> To me, that's a use case where we would wish to have a distinct index on
>>> views.
>>>
>>> Any opinions on this?
>>>
>>> Yours, Stefan
>>>
>>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
quentusrex=# \i sql/view_index_union.sql
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
INSERT 0 50000
INSERT 0 50000
CREATE VIEW
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using named_a_idx on a (cost=0.41..4329.78 rows=51291 width=36)
(actual time=0.078..33.854 rows=50000 loops=1)
Total runtime: 36.226 ms
(2 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using named_b_idx on b (cost=0.41..4385.78 rows=51291 width=36)
(actual time=0.036..27.166 rows=50000 loops=1)
Total runtime: 29.418 ms
(2 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Sort (cost=27371.05..27627.51 rows=102582 width=36) (actual
time=435.566..535.213 rows=100000 loops=1)
Sort Key: a.named
Sort Method: external merge Disk: 4576kB
-> Unique (cost=14230.75..15000.12 rows=102582 width=36) (actual
time=75.540..131.131 rows=100000 loops=1)
-> Sort (cost=14230.75..14487.21 rows=102582 width=36) (actual
time=75.539..102.016 rows=100000 loops=1)
Sort Key: a.id, a.named
Sort Method: external merge Disk: 4584kB
-> Append (cost=0.00..2885.64 rows=102582 width=36) (actual
time=0.005..22.103 rows=100000 loops=1)
-> Seq Scan on a (cost=0.00..929.91 rows=51291 width=36)
(actual time=0.005..6.389 rows=50000 loops=1)
-> Seq Scan on b (cost=0.00..929.91 rows=51291 width=36)
(actual time=0.003..5.811 rows=50000 loops=1)
Total runtime: 541.763 ms
(11 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Sort (cost=26345.23..26601.69 rows=102582 width=36) (actual
time=435.116..534.755 rows=100000 loops=1)
Sort Key: a.named
Sort Method: external merge Disk: 4576kB
-> Unique (cost=14230.75..15000.12 rows=102582 width=36) (actual
time=75.025..130.706 rows=100000 loops=1)
-> Sort (cost=14230.75..14487.21 rows=102582 width=36) (actual
time=75.024..101.363 rows=100000 loops=1)
Sort Key: a.id, a.named
Sort Method: external merge Disk: 4584kB
-> Append (cost=0.00..2885.64 rows=102582 width=36) (actual
time=0.006..21.939 rows=100000 loops=1)
-> Seq Scan on a (cost=0.00..929.91 rows=51291 width=36)
(actual time=0.005..6.187 rows=50000 loops=1)
-> Seq Scan on b (cost=0.00..929.91 rows=51291 width=36)
(actual time=0.004..5.890 rows=50000 loops=1)
Total runtime: 541.167 ms
(11 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=21086.49..21855.86 rows=102582 width=36) (actual
time=133.286..180.493 rows=100000 loops=1)
-> Sort (cost=21086.49..21342.95 rows=102582 width=36) (actual
time=133.285..157.196 rows=100000 loops=1)
Sort Key: a.id, a.named
Sort Method: external merge Disk: 4568kB
-> Append (cost=0.41..9741.38 rows=102582 width=36) (actual
time=0.025..62.696 rows=100000 loops=1)
-> Index Scan using named_a_idx on a (cost=0.41..4329.78
rows=51291 width=36) (actual time=0.025..25.143 rows=50000 loops=1)
-> Index Scan using named_b_idx on b (cost=0.41..4385.78
rows=51291 width=36) (actual time=0.027..27.052 rows=50000 loops=1)
Total runtime: 185.968 ms
(8 rows)
DROP VIEW
DROP TABLE
DROP TABLE
quentusrex=#
create table a (
id integer,
named text);
create index named_a_idx on a (named);
create table b (
id integer,
named text);
create index named_b_idx on b (named);
insert into a (id, named) values (generate_series(1,100000,2), md5(random()::text));
insert into b (id, named) values (generate_series(2,100000,2), md5(random()::text));
create view testing as select * from a union select * from b;
explain analyze select * from a order by named;
explain analyze select * from b order by named;
explain analyze select * from testing order by named;
explain analyze (select id, named from a) union (select id, named from b) order by named;
explain analyze (select id, named from a order by named) union (select id, named from b order by named);
drop view testing;
drop table a;
drop table b;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
