[HACKERS] Another optimizer question
Hi Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) select a.* from test1 a, (select id from test1 order by num) as b where a.id = b.id; There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? -- Dennis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Another optimizer question
On Tue, Jan 27, 2004 at 17:27:25 +0100, Dennis Haney [EMAIL PROTECTED] wrote: Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) Yes. It is important when a limit or distinct on clause is used in a subquery. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Another optimizer question
Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. If you are saying you don't want the sort to occur, why did you write it? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Another optimizer question
Bruno Wolff III wrote: On Tue, Jan 27, 2004 at 17:27:25 +0100, Dennis Haney [EMAIL PROTECTED] wrote: Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) Yes. It is important when a limit or distinct on clause is used in a subquery. Yup, but queries with those are also ignored, so I'm only talking about explicit 'order by' without any fancy stuff ;) -- Dennis
Re: [HACKERS] Another optimizer question
Tom Lane wrote: Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. Then why spend time doing it at all? If you are saying you don't want the sort to occur, I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... -- Dennis
Re: [HACKERS] Another optimizer question
Dennis Haney kirjutas T, 27.01.2004 kell 21:08: Tom Lane wrote: Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. Then why spend time doing it at all? If you are saying you don't want the sort to occur, I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... And why is it written on the outer level of view. AFAIK any select from that view is also free to ignore it. --- Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Another optimizer question
Hannu Krosing [EMAIL PROTECTED] writes: Dennis Haney kirjutas T, 27.01.2004 kell 21:08: I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... And why is it written on the outer level of view. AFAIK any select from that view is also free to ignore it. Indeed. If we decree that we can drop an ORDER BY in a subselect then there is no reason for anyone to write an ORDER BY in a view, because a view is exactly the same thing as a subselect. As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input values. (None of the SQL-standard ones are, of course, but we've frequently seen examples wherein it's convenient to build a user-defined aggregate that is ordering-sensitive.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Another optimizer question
As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input Not to mention our workaround for Max and min (ORDER BY LIMIT) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Another optimizer question
Rod Taylor [EMAIL PROTECTED] writes: As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input Not to mention our workaround for Max and min (ORDER BY LIMIT) Right, although one could reasonably expect that an optimization to drop ORDER BY wouldn't drop it if there were a LIMIT there as well. The planner knows perfectly well that those two clauses interact. The cases that are relevant are where the planner could not realize that dropping the ORDER BY would change the results in an unwanted way. The aggregate function example is interesting because the planner doesn't know whether an aggregate function is order-sensitive or not. (We could imagine extending pg_aggregate and CREATE AGGREGATE to tell that, if we were determined to drop ORDER BY in subselects whenever possible. But I'm not sure that that's the only relevant issue.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] another optimizer question
hi,
can anyone explain me why there are different query plans for select ...
from ... where y!=x and select ... from ... where yx or yx for
integers, please?
see the details below...
thanks,
kuba
db_cen7=# analyze;
ANALYZE
db_cen7=# \d ts19
Table ts19
Column | Type | Modifiers
---+--+
ts19pk___ | integer | not null default
nextval('ts19_ts19pkseq'::text)
ts19datum | timestamp with time zone | not null
ts19zavaz | integer | not null
ts19cislo | integer | not null
ts19text_ | character varying(65536) | not null
ts19idpri | integer | not null
Indexes: ts19_ts19zavaz_idx
Primary key: ts19_pkey
db_cen7=# explain analyze select * from ts19 where ts19zavaz != 7 order by
ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.17..50868.18 rows=10 loops=1)
- Sort (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.16..50868.17 rows=11 loops=1)
- Seq Scan on ts19 (cost=0.00..89635.62 rows=1 width=38)
(actual time=95.99..50852.34 rows=300 loops=1)
Total runtime: 50868.27 msec
db_cen7=# explain analyze select * from ts19 where ts19zavaz 7 or
ts19zavaz 7 order by ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=4.04..4.04 rows=1 width=38) (actual time=1118.28..1118.29
rows=10 loops=1)
- Sort (cost=4.04..4.04 rows=1 width=38) (actual
time=1118.27..1118.28 rows=11 loops=1)
- Index Scan using ts19_ts19zavaz_idx, ts19_ts19zavaz_idx on
ts19 (cost=0.00..4.03 rows=1 width=38) (actual time=0.03..1117.58
rows=300 loops=1)
Total runtime: 1118.40 msec
the runtime times depends on the machine load but generally the second
query is much faster...
more info:
db_cen7=# select count(*) from ts19;
count
-
4190527
(1 row)
db_cen7=# select distinct(ts19zavaz) from ts19;
ts19zavaz
---
3
7
(2 rows)
db_cen7=# select count(*) from ts19 where ts19zavaz = 3;
count
---
300
(1 row)
db_cen7=# select version();
version
---
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] another optimizer question
Jakub Ouhrabka [EMAIL PROTECTED] writes: can anyone explain me why there are different query plans for select ... from ... where y!=x and select ... from ... where yx or yx for integers, please? != isn't an indexable operation. This is not the planner's fault, but a consequence of the index opclass design we inherited from Berkeley. I suppose we could make it an indexable operation --- but there are so few cases where it'd be a win that I'm not excited about it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
