Hi everyone.
I am using postgresql 8.3.7 on Fedora Core 10. I have 1 table called
evaluation which contains about 1 million records, and another called
evaluationentry which contains about 9 million records.
evaluationentry.veto and evaluation.relevancedt both have indexes on
them. I have run ANALYZE against the tables to ensure I have stats. I
execute query:
SELECT T1.evaluationid, MIN(T2.evalresult)
FROM sharemgr.evaluation T1 INNER JOIN
sharemgr.evaluationentry T2 ON (T1.evaluationid = T2.evaluationid)
WHERE T1.relevancedt BETWEEN CAST('2009-06-15 00:00:00' AS TIMESTAMP)
AND CAST('2009-06-15 23:59:59' AS TIMESTAMP)
AND T2.veto = 'Y'
GROUP BY T1.evaluationid
HAVING MIN(T2.evalresult) = 100
and it returns about 10 results (correctly) in about 4 seconds - which
I am more than happy with given the underlying hardware and
virtualization layer.
QUERY PLAN:
===
HashAggregate (cost=197446.95..197454.58 rows=436 width=9) (actual
time=386.877..387.193 rows=10 loops=1)
Filter: (min(t2.evalresult) = 100::numeric)
-> Nested Loop (cost=0.00..197423.83 rows=3082 width=9) (actual
time=0.319..302.310 rows=4438 loops=1)
-> Index Scan using evaluation_i3 on evaluation t1
(cost=0.00..249.97 rows=436 width=4) (actual time=0.130..12.633
rows=634 loops=1)
Index Cond: ((relevancedt >= '2009-12-14
00:00:00'::timestamp without time zone) AND (relevancedt <=
'2009-12-14 23:59:59'::timestamp without time zone))
-> Index Scan using evaluationentry_i1 on evaluationentry t2
(cost=0.00..440.57 rows=933 width=9) (actual time=0.031..0.172 rows=7
loops=634)
Index Cond: (t2.evaluationid = t1.evaluationid)
Filter: (t2.veto = 'Y'::bpchar)
Total runtime: 387.669 ms"
===
I then made this query into a function so I can pass in the 2
timestamps, and return T1.evaluationid as a SETOF INT by doing
FOR matchRecord IN
same query as above
LOOP
RETURN NEXT matchRecord.evaluationid;
END LOOP;
And when I execute the function with the same parameters it takes well
over 5 minutes to execute.
It seems as though inside a function, the optimizer wants to tablescan
my 8M row table. Is there a way that I can see the query plans that my
functions are using?
Any suggestions welcome...
chris
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
