Skip site navigation (1) Skip section navigation (2)

Re: Planner Row Estimate with Function

From: Michael Fork <mfork00(at)yahoo(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Planner Row Estimate with Function
Date: 2009-12-29 18:12:19
Message-ID: 397614.63956.qm@web59004.mail.re1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
Pavel,

Thanks for the suggestion but unfortunately the planner estimate was not really affected:

                                        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(dot)stehule(at)gmail(dot)com>
To: Michael Fork <mfork00(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Tue, December 29, 2009 12:18:52 PM
Subject: Re: [GENERAL] Planner Row Estimate with Function

Hello

2009/12/29 Michael Fork <mfork00(at)yahoo(dot)com>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


In response to

Responses

pgsql-general by date

Next:From: Pavel StehuleDate: 2009-12-29 18:16:10
Subject: Re: Planner Row Estimate with Function
Previous:From: Adrian KlaverDate: 2009-12-29 18:03:19
Subject: Re: pg_dump ERROR, usename "postgres" duplicated

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group