From: | Michael Fork <mfork00(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Planner Row Estimate with Function |
Date: | 2009-12-29 17:08:25 |
Message-ID: | 182210.5109.qm@web59008.mail.re1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have an index scan on a custom function that is returning a wildly incorrect row estimate that is throwing off the rest of the query planning. The result of the function is roughly unique - there are a handful with multiple entries - but the planner is estimating 227,745 rows. I re-ran ANALYZE on the table and the results did not change. Any suggestions on how to get more accurate planner result?
Function definition:
CREATE FUNCTION parsecardidfromreferencecode(text) RETURNS text AS $_$
BEGIN
RETURN SUBSTRING($1 FROM 3 FOR 13);
END;
$_$ LANGUAGE plpgsql IMMUTABLE;
Explain output:
# explain select * from trail.event where type='CREDIT' and parsecardidfromreferencecode(reference_code) = lpad(CAST('57729970' AS text), 13, '0');
QUERY PLAN
-------------------------------------------------------------------------------------------
Index Scan using idx_event_card_id on event (cost=0.25..468642.89 rows=227745 width=104)
Index Cond: (parsecardidfromreferencecode(reference_code) = '0000057729970'::text)
Statistics:
# SELECT null_frac, avg_width, n_distinct, most_common_vals, most_common_freqs, correlation, histogram_bounds FROM pg_stats WHERE tablename = 'idx_event_card_id';
null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | correlation | histogram_bounds
-----------+-----------+------------+------------------+-------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
0.0935673 | 17 | -1 | | | 0.672617 | {0000007932138,0000029448430,0000033432203,0000037841145,0000042171261,0000046399858,0000050938838,0000055122354,0003139385377,1220821134582,1261876426760}
Thanks.
Michael
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2009-12-29 17:18:52 | Re: Planner Row Estimate with Function |
Previous Message | Osvaldo Kussama | 2009-12-29 17:08:05 | Re: cross-database time extract? |