Hello
2009/12/29 Michael Fork <[email protected]>:
> 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;
>
try
CREATE FUNCTION parsecardidfromreferencecode(text) RETURNS text AS $_$
SELECT SUBSTRING($1 FROM 3 FOR 13);
$_$ LANGUAGE sql;
regards
Pavel Stehule
> 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
>
>
> --
> Sent via pgsql-general mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
