4

Is it possible to change the order of the operations of an execution plan manually in postgresql? E.g. if I always want to have the ordering operation before a filtering (although it doesn't make sense in a normal use of postgresql), is it possible to enforce that manually by e.g. changing the internal costs of an operation?

What about if I implement my own function? Is it possible to have such a function always being executed at the very end of the sql statement?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
navige
  • 2,447
  • 3
  • 27
  • 53

3 Answers3

7

Use a subquery or CTE to force certain operations first. Like:

SELECT *
FROM  (
   SELECT *
   FROM   tbl
   LIMIT  10
   ) x
ORDER  BY 1;

You need to understand what you are doing, of course. In the example, I select 10 arbitrary rows and then order them by the first column.
You can use multiple layers of subqueries or multiple CTEs in a row.

Same example as CTE:

WITH x AS (
   SELECT *
   FROM   tbl
   LIMIT  10
   )
SELECT *
FROM   x
ORDER  BY 1;

A subquery is usually faster for simple queries, a CTE offers additional features (like reusing the same CTE in multiple places on different query levels).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you for introducing me to CTE, Erwin Brandstetter! What about multiple CTEs? Are they executed in order? Thank you so much in advance! –  Jul 16 '14 at 01:49
  • 1
    @Gracchus: Not necessarily. Only if you introduce functional dependencies (one CTE referencing the other). CTEs that are not referenced in the outer query may not be called at all. [More ...](http://dba.stackexchange.com/questions/69648/postgresql-seems-to-ignore-raise-exception-in-a-cte/69650#69650) And there are [special rules for data-modifying CTEs ...](http://stackoverflow.com/questions/15809463/postgresql-using-foreign-keys-delete-parent-if-its-not-referenced-by-any-othe/15810159#15810159) – Erwin Brandstetter Jul 16 '14 at 01:59
  • 1
    @Gracchus: You did see in my second link that data-modifying CTEs are *always* executed, right? Only `SELECT` queries can are optimized away if not needed. – Erwin Brandstetter Jul 16 '14 at 03:28
  • Ahhh, thank you for that clarification! My eyes/brain focuses upon a key element, freaks, and abandons details sometimes, lol. Thank you so much again Erwin Brandstetter! –  Jul 16 '14 at 03:40
2

There are more ways - some was showed here, but there are second way, if you would to move function call on end of processing, then just set COST to some higher value. Default for custom functions is 100, but you can set higher value.

CREATE OR REPLACE FUNCTION public.test()
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE COST 1000 -- very expensive function
AS $function$
declare i int;
declare j int;
begin
  i := 1;
  while i < 10000 loop
    j := 1;
    while j < 1000 loop
      j := j + 1;
    end loop;
    i := i + 1;
  end loop;
  return i;
end;
$function$
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
1

About the best you can do without CTEs (which others have explained) is to turn off certain types of operations. This is generally considered dangerous and a method of last resort since it usually points to bugs either in your database (i.e. lacking indexes, not vacuuming enough, too low analyse sampling) or in PostgreSQL code.

But if you want to try it, look up "enable_seqscan" and other settings, see e.g. PostgreSQL documentation.

Ivan Voras
  • 1,895
  • 1
  • 13
  • 20