Ich habe diese PostgreSQL 9.4-Abfrage, die sehr schnell ausgeführt wird (~ 12 ms):

SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email, 
  customers.name,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = 2
ORDER BY
  auth_web_events.id DESC;

Aber wenn ich es in eine Funktion einbinde, läuft die Abfrage sehr langsam durch alle Daten, scheint, dass durch jeden Datensatz läuft, was fehle ich? Ich habe ~ 1M Daten und ich möchte meine Datenbankebene vereinfachen, die das große speichert Abfragen in Funktionen und Ansichten.

CREATE OR REPLACE FUNCTION get_web_events_by_userid(int) RETURNS TABLE(
    id int,
    time_stamp timestamp with time zone,
    description text,
    origin text,
    userlogin text,
    customer text,
    client_ip inet
     ) AS
$func$
SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email AS user, 
  customers.name AS customer,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = $1
ORDER BY
  auth_web_events.id DESC;
  $func$ LANGUAGE SQL;

Der Abfrageplan lautet:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=61.905..61.906 rows=2 loops=1)"
"  Sort Key: auth_web_events.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=61.884..61.893 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=61.874..61.879 rows=2 loops=1)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events  (cost=0.57..4.58 rows=1 width=61) (actual time=61.860..61.860 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"              ->  Index Scan using auth_user_pkey on auth_user  (cost=0.14..8.16 rows=1 width=524) (actual time=0.005..0.005 rows=1 loops=2)"
"                    Index Cond: (id = 2)"
"        ->  Index Scan using customers_id_idx on customers  (cost=0.14..8.16 rows=1 width=222) (actual time=0.004..0.005 rows=1 loops=2)"
"              Index Cond: (id = auth_user.customer_id_fk)"
"Planning time: 0.369 ms"
"Execution time: 61.965 ms"

Ich rufe die Funktion auf diesem Weg an:

SELECT * from get_web_events_by_userid(2)  

Der Abfrageplan für die Funktion:

"Function Scan on get_web_events_by_userid  (cost=0.25..10.25 rows=1000 width=172) (actual time=279107.142..279107.144 rows=2 loops=1)"
"Planning time: 0.038 ms"
"Execution time: 279107.175 ms"

EDIT: Ich ändere nur die Parameter und das Problem bleibt bestehen.
EDIT2: Abfrageplan für die Erwin-Antwort:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=0.048..0.049 rows=2 loops=1)"
"  Sort Key: w.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=0.030..0.037 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=0.023..0.025 rows=2 loops=1)"
"              ->  Index Scan using auth_user_pkey on auth_user u  (cost=0.14..8.16 rows=1 width=524) (actual time=0.011..0.012 rows=1 loops=1)"
"                    Index Cond: (id = 2)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events w  (cost=0.57..4.58 rows=1 width=61) (actual time=0.008..0.008 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"        ->  Index Scan using customers_id_idx on customers c  (cost=0.14..8.16 rows=1 width=222) (actual time=0.003..0.004 rows=1 loops=2)"
"              Index Cond: (id = u.customer_id_fk)"
"Planning time: 0.541 ms"
"Execution time: 0.101 ms"