Re: [PERFORM] Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

On Mon, Nov 28, 2011 at 9:50 AM, Craig Ringer wrote: > On 11/25/2011 06:53 AM, Maxim Boguk wrote: > >> I understand that position. >> However if assumption: " the definition of ORDER BY --- it happens after >> computing the select list, according to the SQL standard" >> is correct, >> then plans

Re: [PERFORM] Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

On 11/25/2011 06:53 AM, Maxim Boguk wrote: I understand that position. However if assumption: " the definition of ORDER BY --- it happens after computing the select list, according to the SQL standard" is correct, then plans like: postgres=# EXPLAIN ANALYZE SELECT * from test order by _data limi

Re: [PERFORM] Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

I understand that position. However if assumption: " the definition of ORDER BY --- it happens after computing the select list, according to the SQL standard" is correct, then plans like: postgres=# EXPLAIN ANALYZE SELECT * from test order by _data limit 10 offset 1000;

Re: [PERFORM] Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

Maxim Boguk writes: > Is here any reason why Postgresql calculates subqueries/storable procedures > in select list before applying ORDER BY / LIMIT? Well, that's the definition of ORDER BY --- it happens after computing the select list, according to the SQL standard. We try to optimize this in s

[PERFORM] Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

Is here any reason why Postgresql calculates subqueries/storable procedures in select list before applying ORDER BY / LIMIT? I talking about cases like: SELECT *, (some very slow subquery or slow storable stable/immutable procedure like xml processing) FROM some_table ORDER BY some_field (unrelat

Re: [PERFORM] Some question

Hi, here are they: select * from t_route_type; ID;description;type 1;"stojim";0 2;"idem";1 explain analyze SELECT * FROM t_route WHERE t_route.route_type_fk = 1 limit 4; "

Re: [PERFORM] Some question

Ľubomír Varga wrote: > SELECT * FROM t_route > WHERE t_route.route_type_fk = 1 > limit 4; This one scanned the t_route table until it found four rows that matched. It apparently didn't need to look at very many rows to find the four matches, so it was fast. > SELECT * FROM t_route > WH

Re: [PERFORM] Some question

Scott Marlowe wrote: 2010/3/31 Ľubomír Varga : Hi, stright to my "problem": If I try to select constant 1 from table with two rows, it will be something like this: explain SELECT * FROM t_route WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type = 2) limit 4;

Re: [PERFORM] Some question

*ubomír Varga wrote: > Hi, stright to my "problem": Please show the exact problem query and the results of running it with EXPLAIN ANALYZE, along with the other information suggested here: http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- Sent via pgsql-performance mailing list

Re: [PERFORM] Some question

2010/3/31 Ľubomír Varga : > Hi, stright to my "problem": > If I try to select constant 1 from table with two rows, it will be something > like this: > > explain > SELECT * FROM t_route >        WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type > = 2) >        limit 4; > > "Limi

[PERFORM] Some question

Hi, stright to my "problem": explain SELECT * FROM t_route WHERE t_route.route_type_fk = 1 limit 4; "Limit (cost=0.00..0.88 rows=4 width=2640)" " -> Seq Scan on t_route (cost=0.00..118115.25 rows=538301 width=2640)" "Filter: (route_type_fk = 1)" If I try to select c