pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedViews with unions
13+ messages / 7 participants
[nested] [flat]
* Views with unions
@ 2003-02-15 23:48 Mariusz <manieq@idea.net.pl>
0 siblings, 1 reply; 13+ messages in thread
From: Mariusz Czułada @ 2003-02-15 23:48 UTC (permalink / raw)
To: pgsql-performance
Hello,
While testing multi-select views I found some problems. Here are details. I have 3 tables and I created a view on them:
create view view123 as
select key, value from tab1 where key=1
union all
select key, value from tab2 where key=2
union all
select key, value from tab3 where key=3;
When querying with no conditions, I get plan:
test_db=# explain analyze select key, value from view123;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.19 rows=15 width=11) (actual time=0.15..1.00 rows=15 loops=1)
-> Append (cost=0.00..3.19 rows=15 width=11) (actual time=0.14..0.80 rows=15 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.06 rows=5 width=11) (actual time=0.13..0.30 rows=5 loops=1)
-> Seq Scan on tab1 (cost=0.00..1.06 rows=5 width=11) (actual time=0.11..0.22 rows=5 loops=1)
Filter: ("key" = 1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.06 rows=5 width=11) (actual time=0.07..0.22 rows=5 loops=1)
-> Seq Scan on tab2 (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
Filter: ("key" = 2)
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.06 rows=5 width=11) (actual time=0.06..0.22 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
Filter: ("key" = 3)
Total runtime: 1.57 msec
(12 rows)
But with "key = 3":
test_db# explain analyze select key, value from view123 where key=3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
-> Append (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
-> Seq Scan on tab1 (cost=0.00..1.07 rows=1 width=11) (actual time=0.17..0.17 rows=0 loops=1)
Filter: (("key" = 1) AND ("key" = 3))
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on tab2 (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
Filter: (("key" = 2) AND ("key" = 3))
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
Filter: (("key" = 3) AND ("key" = 3))
Total runtime: 1.22 msec
(12 rows)
I would expect, that false filters, like (("key" = 1) AND ("key" = 3)) will make table full scan unnecessary. So I expected plan like:
test_db# explain analyze select key, value from view123 where key=3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
-> Append (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
-> Result (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
^^^^^^^^^^^ my change
Filter: (("key" = 1) AND ("key" = 3)) [always false]
^^^^^^^^^^^ my change
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
-> Result (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
^^^^^^^^^^^ my change
Filter: (("key" = 2) AND ("key" = 3)) [always false]
^^^^^^^^^^^ my change
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
Filter: (("key" = 3) AND ("key" = 3))
Total runtime: 1.22 msec
(12 rows)
No "Seq Scan" on tables where filter is false.
I realize that's how it works now, but:
a) is there any way to avoid such scans?
b) is it possible (or in TODO) to optimize for such cases?
Regards,
Mariusz Czułada
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views with unions
@ 2003-02-16 03:54 Josh Berkus <josh@agliodbs.com>
parent: Mariusz <manieq@idea.net.pl>
0 siblings, 2 replies; 13+ messages in thread
From: Josh Berkus @ 2003-02-16 03:54 UTC (permalink / raw)
To: Mariusz Czułada <manieq@idea.net.pl>; pgsql-performance
Mariusz,
> While testing multi-select views I found some problems. Here are details. I
> have 3 tables and I created a view on them:
What version of PostgreSQL are you using? UNION views optimized extremely
poorly through 7.2.4; things have been improved in 7.3
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views with unions
@ 2003-02-16 08:19 Mariusz <manieq@idea.net.pl>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 13+ messages in thread
From: Mariusz Czułada @ 2003-02-16 08:19 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Hi,
Dnia nie 16. lutego 2003 04:54, Josh Berkus napisał:
>
> What version of PostgreSQL are you using? UNION views optimized extremely
> poorly through 7.2.4; things have been improved in 7.3
PostgreSQL 7.3 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.2 (self
compiled on SunBlade 100).
Mariusz
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views with unions
@ 2003-02-16 18:05 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 13+ messages in thread
From: Stephan Szabo @ 2003-02-16 18:05 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Mariusz Czułada <manieq@idea.net.pl>; pgsql-performance
On Sat, 15 Feb 2003, Josh Berkus wrote:
> Mariusz,
>
> > While testing multi-select views I found some problems. Here are details. I
> > have 3 tables and I created a view on them:
>
> What version of PostgreSQL are you using? UNION views optimized extremely
> poorly through 7.2.4; things have been improved in 7.3
Yeah, but I think what he's hoping is that it'll notice that
"key=1 and key=3" would be noticed as a false condition so that it doesn't
scan those tables since a row presumably can't satisify both. The question
would be, is the expense of checking the condition for all queries
greater than the potential gain for these sorts of queries. In addition,
you'd have to be careful to make it work correctly with operator
overloading, since someone could make operators whose semantics in
cross-datatype comparisons are wierd.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views with unions
@ 2003-02-16 18:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-02-16 18:51 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Mariusz Czułada <manieq@idea.net.pl>; pgsql-performance
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Yeah, but I think what he's hoping is that it'll notice that
> "key=1 and key=3" would be noticed as a false condition so that it doesn't
> scan those tables since a row presumably can't satisify both. The question
> would be, is the expense of checking the condition for all queries
> greater than the potential gain for these sorts of queries.
Yes, this is the key point: we won't put in an optimization that wins on
a small class of queries unless there is no material cost added for
planning cases where it doesn't apply.
> In addition, you'd have to be careful to make it work correctly with
> operator overloading, since someone could make operators whose
> semantics in cross-datatype comparisons are wierd.
In practice we would restrict such deductions to mergejoinable =
operators, which are sufficiently semantics-constrained that I think
you can treat equality at face value.
Actually, in CVS tip we are on the hairy edge of being able to do this:
generate_implied_equalities() actually detects that the given conditions
imply that two constants are equal. But it doesn't do anything with the
knowledge, because I couldn't figure out just what to do --- it's not
always correct to add a "WHERE false" constraint to the top level, but
where exactly do we add it? Exactly which relations are guaranteed to
produce zero rows in such a case? (When there are outer joins in the
picture, zero rows out of some relations doesn't mean zero rows out
overall.) And how do we exploit that knowledge once we've got it?
It'd be a pretty considerable amount of work to optimize a plan tree
fully for this sort of thing (eg, suppressing unnecessary joins), and
I doubt it's worth the trouble.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views with unions
@ 2003-02-16 20:27 Mariusz <manieq@idea.net.pl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Mariusz Czułada @ 2003-02-16 20:27 UTC (permalink / raw)
To: pgsql-performance
Dnia nie 16. lutego 2003 19:51, Tom Lane napisał:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Yeah, but I think what he's hoping is that it'll notice that
> > "key=1 and key=3" would be noticed as a false condition so that it
> > doesn't scan those tables since a row presumably can't satisify both. The
Yes, that is what I expected.
>
> Yes, this is the key point: we won't put in an optimization that wins on
> a small class of queries unless there is no material cost added for
> planning cases where it doesn't apply.
>
> > In addition, you'd have to be careful to make it work correctly with
> > operator overloading, since someone could make operators whose
> > semantics in cross-datatype comparisons are wierd.
>
> It'd be a pretty considerable amount of work to optimize a plan tree
> fully for this sort of thing (eg, suppressing unnecessary joins), and
> I doubt it's worth the trouble.
Ok, perhaps I should give some explaination about my case.
We are gathering lots of log data in a few tables. Each table grows by some
300.000...500.000 rows a day. With average row size of 100 bytes we get up to
50MB of data per day. Keeping data for 1 year only gives us some 18GB per
table. Also, in each table there is a field with very low cardinality (5..20
unique values). This field appears in most of our queries to the table, in
'where' clause (mostly key_field = 5, some times key_field in (1,2,3)).
What I was thinking of is to implement some kind of horizontal table
partitioning. I wanted to split physical storage of data to few smaller
tables. In my case it could be come 12 subtables, 1..2 GB each. Now, with
'union-all' view (and lots of rules, of course) I could simultate partitioned
table as Oracle implements it. IMHO while querying this view (supertable) for
one or few 'key_field' values it should be much faster for scan 5 GB of 3
partitions (subtables) than 18GB for one big table.
I realize it is not the only solution. Perhaps it could be implemented by a
function taking key_filed value and returning all rows from proper table
(p[lus functions for insert/update/delete). Perhaps application code (log
feeder and report module) could be recoded to know about splitted tables.
Still I think it is 'elegant' and clear.
I wait for your comments,
Mariusz Czulada
^ permalink raw reply [nested|flat] 13+ messages in thread
* Views With Unions
@ 2003-07-31 22:24 Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 1 reply; 13+ messages in thread
From: Christopher Browne @ 2003-07-31 22:24 UTC (permalink / raw)
To: pgsql-performance
This is stepping back quite a while; let me point people to the thread
of 2003-02 where Mariusz Czu\x{0142}ada <manieq@idea.net.pl> was
looking for a way of optimizing a VIEW that was a UNION.
<http://archives.postgresql.org/pgsql-performance/2003-02/msg00095.php;
The subject has come up a few times through PostgreSQL history, and
I'd imagine to think I may have a little something new to offer to it.
Let's consider a table used to store log information:
create table log_table (
request_time timestamp with time zone,
object character varying, -- What they asked for
request_type character(8), -- What they did to it
request_size integer,
requestor inet,
request_status integer,
result_size integer,
request_detail character varying
);
create index log_times on log_table(request_time);
create index log_object on log_table(object);
Every time "something happens," an entry goes into this table.
Unfortunately, the table is likely to grow to tremendous size, over
time, and there are all sorts of troublesome things about purging it:
-> Fragmentation may waste space and destroy the usefulness of
indices;
-> Deleting data row by row will cause replication logic to go mad,
as triggers get invoked for every single row modified;
-> The action of deletion will draw the data we just decided was
_useless_ into memory, injuring cache utilization badly as we fill
the cache with trash.
The obvious thought: Create several tables, and join them together
into a view. So instead of log_table being a table, we have
log_table_1 thru log_table_3, each with the schema describe above, and
define the view:
create view log_table as select * from log_table_1 union all
select * from log_table_2 union all
select * from log_table_3;
It's easy enough (modulo a little debugging and pl/pgsql work :-)) to
turn this into an updatable view so that inserts into log_table use a
different log table every (day|week|month). And we can TRUNCATE the
eldest one, which is a cheap operation.
This approach also resembles the way the "O guys" handle partitioned
tables, so it's not merely about "logs."
Unfortunately, selects on the VIEW are, at present, unable to make use
of the indices. So if we want all log entries for June 11th, the
query:
select * from log_table where request_time between 'june 11 2003' and
'june 12 2003';
returns a plan:
Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314)
-> Append (cost=0.00..10950.26 rows=177126 width=314)
-> Subquery Scan *SELECT* 1 (cost=0.00..3089.07 rows=50307 width=71)
-> Seq Scan on log_table_1 (cost=0.00..3089.07 rows=50307 width=71)
-> Subquery Scan *SELECT* 2 (cost=0.00..602.92 rows=9892 width=314)
-> Seq Scan on log_table_2 (cost=0.00..602.92 rows=9892 width=314)
-> Subquery Scan *SELECT* 3 (cost=0.00..2390.09 rows=39209 width=314)
-> Seq Scan on log_table_3 (cost=0.00..2390.09 rows=39209 width=314)
In effect, the query is materialized into:
select * from
(select * from log_table_1 union all select * from log_table_2
union all select * from log_table_3) as merger
where [request_time between 'june 11 2003' and 'june 12 2003'];
What would perform better would be to attach the WHERE clause to each
of the union members. (Everyone stop and sing "Solidarity Forever"
:-))
E.g.:
select * from
(
select * from log_table_1 where request_time between 'june 11 2003' and 'june 12 2003' union all
select * from log_table_2 where request_time between 'june 11 2003' and 'june 12 2003' union all
select * from log_table_3 where request_time between 'june 11 2003' and 'june 12 2003' union all
) as merged_version;
Subquery Scan merged_version (cost=0.00..947.04 rows=247 width=314) (actual time=55.86..1776.42 rows=20124 loops=1)
-> Append (cost=0.00..947.04 rows=247 width=314) (actual time=55.84..1483.60 rows=20124 loops=1)
-> Subquery Scan *SELECT* 1 (cost=0.00..3.02 rows=1 width=71) (actual time=55.83..289.81 rows=3422 loops=1)
-> Index Scan using log_table_1_trans_on_idx on log_table_1 (cost=0.00..3.02 rows=1 width=71) (actual time=55.80..239.84 rows=3422 loops=1)
-> Subquery Scan *SELECT* 2 (cost=0.00..191.38 rows=49 width=314) (actual time=62.32..1115.15 rows=16702 loops=1)
-> Index Scan using log_table_2_trans_on_idx on log_table_2 (cost=0.00..191.38 rows=49 width=314) (actual time=62.29..873.63 rows=16702 loops=1)
-> Subquery Scan *SELECT* 3 (cost=0.00..752.64 rows=196 width=314) (actual time=26.69..26.69 rows=0 loops=1)
-> Index Scan using log_table_3_trans_on_idx on log_table_3 (cost=0.00..752.64 rows=196 width=314) (actual time=26.69..26.69 rows=0 loops=1)
Total runtime: 1806.39 msec
Which is nice and quick, as it cuts each set down to size _before_
merging them.
Mariusz had been looking, back in February, for an optimization that
would, in effect, throw away the UNION ALL clauses that were
unnecessary. Tom Lane and Stephan Szabo, in discussing this,
observed, quite rightly, that this is liable to be an obscure sort of
optimization:
Tom Lane writes:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Yeah, but I think what he's hoping is that it'll notice that
> > "key=1 and key=3" would be noticed as a false condition so that it doesn't
> > scan those tables since a row presumably can't satisify both. The question
> > would be, is the expense of checking the condition for all queries
> > greater than the potential gain for these sorts of queries.
> Yes, this is the key point: we won't put in an optimization that
> wins on a small class of queries unless there is no material cost
> added for planning cases where it doesn't apply.
In contrast, I would argue that adding the WHERE clause in as an extra
condition on each of the UNION subqueries is an optimization that is
likely to win in _most_ cases.
It helps with the example I illustrated; it would help with Mariusz'
scenario, not by outright eliminating UNION subqueries, but rather by
making their result sets empty.
select key, value from view123 where key = 2
transforms into...
select key, value from tab1 where key=1 [and key = 2]
union all
select key, value from tab2 where key=2 [and key = 2]
union all
select key, value from tab3 where key=3 [and key = 2];
The generalization is that:
select * from
(select [fields1] from t1 where [cond1] (UNION|UNION ALL|INTERSECT)
select [fields2] from t2 where [cond2] (UNION|UNION ALL|INTERSECT)
...
select [fieldsn] from tn where [condn]) as COMBINATION
WHERE [globalcond];
is equivalent to:
select * from
(select [fields1] from t1 where ([cond1]) and [globalcond] (UNION|UNION ALL|INTERSECT)
select [fields2] from t2 where ([cond2]) and [globalcond] (UNION|UNION ALL|INTERSECT)
...
select [fieldsn] from tn where ([condn]) and [globalcond]
) as COMBINATION;
[globalcond] has to be expressed in terms of the fields available for
each subquery, but that already needs to be true, because the global
condition at present is being applied to the fields that are given by
the UNION/INTERSECT/UNION ALL.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-07-31 22:34 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 2 replies; 13+ messages in thread
From: Stephan Szabo @ 2003-07-31 22:34 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: pgsql-performance
On Thu, 31 Jul 2003, Christopher Browne wrote:
> select * from log_table where request_time between 'june 11 2003' and
> 'june 12 2003';
>
> returns a plan:
> Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314)
> -> Append (cost=0.00..10950.26 rows=177126 width=314)
> -> Subquery Scan *SELECT* 1 (cost=0.00..3089.07 rows=50307 width=71)
> -> Seq Scan on log_table_1 (cost=0.00..3089.07 rows=50307 width=71)
> -> Subquery Scan *SELECT* 2 (cost=0.00..602.92 rows=9892 width=314)
> -> Seq Scan on log_table_2 (cost=0.00..602.92 rows=9892 width=314)
> -> Subquery Scan *SELECT* 3 (cost=0.00..2390.09 rows=39209 width=314)
> -> Seq Scan on log_table_3 (cost=0.00..2390.09 rows=39209 width=314)
What version are you using? In 7.3 and up it should be willing to
consider moving the clause down, unless there's something like a type
mismatch (because in that case it may not be equivalent without a bunch
more work on the clause).
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-08-01 03:40 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2003-08-01 03:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance
Stephan Szabo wrote:
>On Thu, 31 Jul 2003, Christopher Browne wrote:
>
>
>
>> select * from log_table where request_time between 'june 11 2003' and
>> 'june 12 2003';
>>
>>returns a plan:
>>Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314)
>> -> Append (cost=0.00..10950.26 rows=177126 width=314)
>> -> Subquery Scan *SELECT* 1 (cost=0.00..3089.07 rows=50307 width=71)
>> -> Seq Scan on log_table_1 (cost=0.00..3089.07 rows=50307 width=71)
>> -> Subquery Scan *SELECT* 2 (cost=0.00..602.92 rows=9892 width=314)
>> -> Seq Scan on log_table_2 (cost=0.00..602.92 rows=9892 width=314)
>> -> Subquery Scan *SELECT* 3 (cost=0.00..2390.09 rows=39209 width=314)
>> -> Seq Scan on log_table_3 (cost=0.00..2390.09 rows=39209 width=314)
>>
>>
>
>What version are you using? In 7.3 and up it should be willing to
>consider moving the clause down, unless there's something like a type
>mismatch (because in that case it may not be equivalent without a bunch
>more work on the clause).
>
Dear Chris,
I had the same problem(type mismatch) and it was solved finally.
check the list
"factoring problem ... " subject only 2 weeks back .
regds
mallah.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-08-01 12:20 Christopher Browne <cbbrowne@libertyrms.info>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 13+ messages in thread
From: Christopher Browne @ 2003-08-01 12:20 UTC (permalink / raw)
To: sszabo@megazone.bigpanda.com; +Cc: pgsql-performance
Stephan Szabo said:
>
> On Thu, 31 Jul 2003, Christopher Browne wrote:
>
>> select * from log_table where request_time between 'june 11 2003'
>> and
>> 'june 12 2003';
>>
>> returns a plan:
>> Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314)
>> -> Append (cost=0.00..10950.26 rows=177126 width=314)
>> -> Subquery Scan *SELECT* 1 (cost=0.00..3089.07 rows=50307
>> width=71)
>> -> Seq Scan on log_table_1 (cost=0.00..3089.07
>> rows=50307 width=71)
>> -> Subquery Scan *SELECT* 2 (cost=0.00..602.92 rows=9892
>> width=314)
>> -> Seq Scan on log_table_2 (cost=0.00..602.92
>> rows=9892 width=314)
>> -> Subquery Scan *SELECT* 3 (cost=0.00..2390.09 rows=39209
>> width=314)
>> -> Seq Scan on log_table_3 (cost=0.00..2390.09
>> rows=39209 width=314)
>
> What version are you using? In 7.3 and up it should be willing to
> consider moving the clause down, unless there's something like a type
> mismatch (because in that case it may not be equivalent without a bunch
> more work on the clause).
That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
Which provides four findings:
1. On 7.2.4, adding additional type info just doesn't help, fitting with
the notion that, consistent with your comment, improvement wouldn't happen
earlier than 7.3.
There's no help on 7.2 :-(, and the system I'm initially most interested
in using this on is still on 7.2.
2. When I retried on 7.4, it _did_ find search paths based on Index Scan,
when I added in additional type information. So the optimization I was
wishing for _is_ there :-). In the longer term, that's very good news.
3. I'll have to test this out on 7.3.4, now, as I hadn't, and it sounds
as though that is an interesting case.
4. It's often necessary to expressly specify type information in queries
to get the optimizer to do the Right Thing.
--
(reverse (concatenate 'string "ofni.smrytrebil@" "enworbbc"))
<http://dev6.int.libertyrms.info/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-08-01 15:10 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 1 reply; 13+ messages in thread
From: Stephan Szabo @ 2003-08-01 15:10 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: pgsql-performance
On Fri, 1 Aug 2003, Christopher Browne wrote:
> Stephan Szabo said:
> >
> >
> > What version are you using? In 7.3 and up it should be willing to
> > consider moving the clause down, unless there's something like a type
> > mismatch (because in that case it may not be equivalent without a bunch
> > more work on the clause).
>
> That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
>
> Which provides four findings:
>
> 1. On 7.2.4, adding additional type info just doesn't help, fitting with
> the notion that, consistent with your comment, improvement wouldn't happen
> earlier than 7.3.
>
> There's no help on 7.2 :-(, and the system I'm initially most interested
> in using this on is still on 7.2.
If you really wanted you could try going back and finding the diffs
associated with this in the CVS history or committers archives and see if
you can make equivalent changes to 7.2, but that's possibly going to be
difficult.
> 2. When I retried on 7.4, it _did_ find search paths based on Index Scan,
> when I added in additional type information. So the optimization I was
> wishing for _is_ there :-). In the longer term, that's very good news.
>
> 3. I'll have to test this out on 7.3.4, now, as I hadn't, and it sounds
> as though that is an interesting case.
>
> 4. It's often necessary to expressly specify type information in queries
> to get the optimizer to do the Right Thing.
Especially for cases like this. It takes the safer route of not pushing
things down when it's not sure if pushing down might change the semantics
(for example if a union piece has a different type from the union
output, simply pushing clauses down unchanged could change the results)
Tom would probably be willing to relax conditions if it could be proven
safe even for the wierd outlying cases with char and varchar and such.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-08-01 15:48 Christopher Browne <cbbrowne@libertyrms.info>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 13+ messages in thread
From: Christopher Browne @ 2003-08-01 15:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 1 Aug 2003, Christopher Browne wrote:
>> Stephan Szabo said:
>> > What version are you using? In 7.3 and up it should be willing to
>> > consider moving the clause down, unless there's something like a type
>> > mismatch (because in that case it may not be equivalent without a bunch
>> > more work on the clause).
>>
>> That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
>>
>> Which provides four findings:
>>
>> 1. On 7.2.4, adding additional type info just doesn't help, fitting with
>> the notion that, consistent with your comment, improvement wouldn't happen
>> earlier than 7.3.
>>
>> There's no help on 7.2 :-(, and the system I'm initially most interested
>> in using this on is still on 7.2.
>
> If you really wanted you could try going back and finding the diffs
> associated with this in the CVS history or committers archives and see if
> you can make equivalent changes to 7.2, but that's possibly going to be
> difficult.
Somehow I don't think that'll fly; I have taken a brief look at some
of the optimizer code, and I very much don't want to leap into that at
the moment. (I don't imagine I'd be able to muster much enthusiasm
for the idea from others that are involved, either. More likely, I'm
understating the probable opposition to the idea... :-))
I was hoping there would be some help on 7.2, but can live without it.
This approach to improving log purgeability is NOT the sort of thing
that you deploy on a day's notice because it seems like a "neat idea."
If it waits a couple months to be implemented, that's doubtless OK.
>> 2. When I retried on 7.4, it _did_ find search paths based on Index Scan,
>> when I added in additional type information. So the optimization I was
>> wishing for _is_ there :-). In the longer term, that's very good news.
>>
>> 3. I'll have to test this out on 7.3.4, now, as I hadn't, and it sounds
>> as though that is an interesting case.
It turns out nicely on 7.3.4, using index scans for the subqueries in
the query:
select count(*) from log_table where event_date between
'2003-04-01' and '2003-05-01';
Which is a Good Thing.
>> 4. It's often necessary to expressly specify type information in
>> queries to get the optimizer to do the Right Thing.
>
> Especially for cases like this. It takes the safer route of not
> pushing things down when it's not sure if pushing down might change
> the semantics (for example if a union piece has a different type
> from the union output, simply pushing clauses down unchanged could
> change the results)
>
> Tom would probably be willing to relax conditions if it could be
> proven safe even for the wierd outlying cases with char and varchar
> and such.
Evidently the dates of the form '2003-04-01' and such are getting
types promoted properly enough. I don't see anything to "lobby" for
at this point.
The DOMAIN case I mentioned the other day had something odd going on
that LOST the type information associated with the domain. Albeit
that was on 7.3, whereas the changes in DOMAIN functionality that make
them meaningfully useful come in 7.4...
--
let name="cbbrowne" and tld="libertyrms.info" in name ^ "@" ^ tld;;
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Views With Unions
@ 2003-08-01 16:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2003-08-01 16:51 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance
Christopher Browne <cbbrowne@libertyrms.info> writes:
> The DOMAIN case I mentioned the other day had something odd going on
> that LOST the type information associated with the domain. Albeit
> that was on 7.3, whereas the changes in DOMAIN functionality that make
> them meaningfully useful come in 7.4...
Domains were a work-in-progress in 7.3, and to some extent still are.
Please try to test out 7.4beta and let us know about deficiencies you
find.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-08-01 16:51 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-15 23:48 Views with unions Mariusz <manieq@idea.net.pl>
2003-02-16 03:54 ` Josh Berkus <josh@agliodbs.com>
2003-02-16 08:19 ` Mariusz <manieq@idea.net.pl>
2003-02-16 18:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-16 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-16 20:27 ` Mariusz <manieq@idea.net.pl>
2003-07-31 22:24 Views With Unions Christopher Browne <cbbrowne@libertyrms.info>
2003-07-31 22:34 ` Re: Views With Unions Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-01 03:40 ` Re: Views With Unions Rajesh Kumar Mallah <mallah@trade-india.com>
2003-08-01 12:20 ` Re: Views With Unions Christopher Browne <cbbrowne@libertyrms.info>
2003-08-01 15:10 ` Re: Views With Unions Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-01 15:48 ` Re: Views With Unions Christopher Browne <cbbrowne@libertyrms.info>
2003-08-01 16:51 ` Re: Views With Unions 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