I tried the trick below and the planner estimate was roughly the same:
springboard_v2=# explain select * from trail.event where type='CREDIT' and
lpad(CAST('57729970' AS text), 13, '0') =
ANY(parsecardidfromreferencecode(reference_code));
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on event (cost=1669366.06..15120311.84 rows=2178778
width=103)
Recheck Cond: (type = 'CREDIT'::text)
Filter: ('0000057729970'::text = ANY
(parsecardidfromreferencecode(reference_code)))
-> Bitmap Index Scan on idx_event_card_id (cost=0.00..1668821.37
rows=44565021 width=0)
Thanks.
Michael
----- Original Message ----
From: Pavel Stehule <[email protected]>
To: Michael Fork <[email protected]>
Cc: [email protected]
Sent: Tue, December 29, 2009 1:16:10 PM
Subject: Re: [GENERAL] Planner Row Estimate with Function
2009/12/29 Michael Fork <[email protected]>:
> 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 <[email protected]>
> To: Michael Fork <[email protected]>
> Cc: [email protected]
> Sent: Tue, December 29, 2009 12:18:52 PM
> Subject: Re: [GENERAL] Planner Row Estimate with Function
>
> 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
