From: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
---|---|
To: | Postgres-General <pgsql-general(at)postgresql(dot)org> |
Subject: | Problem with index when using function |
Date: | 2008-01-01 04:15:50 |
Message-ID: | 4779BE76.8070204@lorenso.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | D. Dante Lorenso | 2008-01-01 04:21:25 | Re: Problem with index when using function |
Previous Message | dvanatta | 2007-12-31 23:06:21 | CREATE TYPE and pgAdmin III |