2009/12/29 Michael Fork <mfork00@xxxxxxxxx>: > Pavel, > > Thanks for the suggestion but unfortunately the planner estimate was not really affected: any string estimation are not exact. you can use following dirty trick: http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Using_IMMUTABLE_functions_as_hints_for_the_optimizer Regards Pavel > > QUERY PLAN > ------------------------------------------------------------------------------------------- > Index Scan using idx_event_card_id on event (cost=0.00..401311.59 rows=223890 width=103) > Index Cond: ("substring"(reference_code, 3, 13) = '0000057729970'::text) > (2 rows) > Thanks. > > > Michael > > > > ----- Original Message ---- > From: Pavel Stehule <pavel.stehule@xxxxxxxxx> > To: Michael Fork <mfork00@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Sent: Tue, December 29, 2009 12:18:52 PM > Subject: Re: Planner Row Estimate with Function > > Hello > > 2009/12/29 Michael Fork <mfork00@xxxxxxxxx>: >> 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 (pgsql-general@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
