Problem with index when using function
От | D. Dante Lorenso |
---|---|
Тема | Problem with index when using function |
Дата | |
Msg-id | 4779BE76.8070204@lorenso.com обсуждение исходный текст |
Ответы |
Re: Problem with index when using function
|
Список | pgsql-general |
All, I have a weird situation where my index IS used when I use a query that hard-codes a value but it does NOT use the index when the value is returned from a PGSQL function: ====================================================================== DOES NOT WORK ====================================================================== svcprod=# EXPLAIN SELECT COALESCE(SUM(start_count), 0) AS start_count, COALESCE(SUM(end_count), 0) AS end_count, COALESCE(SUM(total_playtime), 0) AS total_playtime FROM audio_file_stats WHERE afile_id = num2id(1173, 1075, 'audio_file', 'audio_id', 'afile_id')::bigint; QUERY PLAN ---------------------------------------------------------------------- Aggregate (cost=118677.35..118677.36 rows=1 width=19) -> Seq Scan on audio_file_stats (cost=0.00..118675.33 rows=268 width=19) Filter: (afile_id = num2id(1173::bigint, 1075::bigint, 'audio_file'::character varying, 'audio_id'::character varying, 'afile_id'::character varying)) (3 rows) ====================================================================== WORKS ====================================================================== byoaudio=# EXPLAIN SELECT COALESCE(SUM(start_count), 0) AS start_count, COALESCE(SUM(end_count), 0) AS end_count, COALESCE(SUM(total_playtime), 0) AS total_playtime FROM audio_file_stats WHERE afile_id = 48702; QUERY PLAN ---------------------------------------------------------------------- Aggregate (cost=672.69..672.70 rows=1 width=19) -> Index Scan using audio_file_stats_idx_afile_id on audio_file_stats (cost=0.00..670.73 rows=261 width=19) Index Cond: (afile_id = 48702) (3 rows) ====================================================================== The function I use is defined as using returning a BIGINT which is the same datatype as is used by the index: FUNCTION "public"."num2id" (in_acct_id bigint, in_value bigint, in_table_name varchar, in_input_column varchar, in_output_column varchar) RETURNS bigint Can someone help explain what is being done wrong here? I'm using 8.2.4. -- Dante
В списке pgsql-general по дате отправления: