Suggestion on table analyze
Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contain GIS data. While analyzing the table getting below NOTICE. It seems is pretty understanding, but needs help on the below points. 1 . What might be the reason for getting the NOTICE?. 2. Is this lead to any problems in the future?. ANALYZE SCHEMA.TABLE; NOTICE: no non-null/empty features, unable to compute statistics NOTICE: no non-null/empty features, unable to compute statistics Query returned successfully with no result in 1.1 secs. Thanks for your support. Regards, PostgAnn.
Suggestion on index creation for TEXT data field
Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contains GIS data and we are trying to creating an index on the column which is having an avg width of 149bytes. CREATE INDEX index_idx ON SCHEMA.TABLE USING btree (column); ERROR: index row size 2976 exceeds maximum 2712 for index "index_idx" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full-text indexing. Could you please suggest on below queries. 1. How to solve the issue?. 2. What type of index is the best suited for this type of data?. Thanks for your support. Regards, PostgAnn.
Re: Suggestion on index creation for TEXT data field
On Thu, May 21, 2020 at 7:28 AM postgann2020 s wrote: > which is having an avg width of 149bytes. > The average is meaningless if your maximum value exceeds a limit. 2. What type of index is the best suited for this type of data?. > And what type of data exactly are we talking about. "TEXT" is not a useful answer. If the raw data is too large no index is going to be "best" - as the hint suggests you either need to drop the idea of indexing the column altogether or apply some function to the raw data and then index the result. David J.
Re: Suggestion on table analyze
On 5/21/20 7:18 AM, postgann2020 s wrote:
Hi Team,
Thanks for your support.
Could you please suggest on below query.
Environment
PostgreSQL: 9.5.15
Postgis: 2.2.7
Mostly table contain GIS data.
While analyzing the table getting below NOTICE. It seems is pretty
understanding, but needs help on the below points.
1 . What might be the reason for getting the NOTICE?.
2. Is this lead to any problems in the future?.
ANALYZE SCHEMA.TABLE;
NOTICE: no non-null/empty features, unable to compute statistics
NOTICE: no non-null/empty features, unable to compute statistics
Query returned successfully with no result in 1.1 secs.
This is coming from PostGIS:
postgis/gserialized_estimate.c:
/* If there's no useful features, we can't work out stats */
if ( ! notnull_cnt )
{
elog(NOTICE, "no non-null/empty features, unable to
compute statistics");
stats->stats_valid = false;
return;
}
You might find more information from here:
https://postgis.net/support/
Though FYI PostGIS 2.2.7 is past EOL:
https://postgis.net/source/
Thanks for your support.
Regards,
PostgAnn.
--
Adrian Klaver
[email protected]
Re: Suggestion on index creation for TEXT data field
Hi David, Thanks for your email. >And what type of data exactly are we talking about. ==> Column is stroing GIS data. Regards, PostgAnn. On Thu, May 21, 2020 at 8:06 PM David G. Johnston < [email protected]> wrote: > On Thu, May 21, 2020 at 7:28 AM postgann2020 s > wrote: > >> which is having an avg width of 149bytes. >> > > The average is meaningless if your maximum value exceeds a limit. > > 2. What type of index is the best suited for this type of data?. >> > > And what type of data exactly are we talking about. "TEXT" is not a > useful answer. > > If the raw data is too large no index is going to be "best" - as the hint > suggests you either need to drop the idea of indexing the column altogether > or apply some function to the raw data and then index the result. > > David J. > >
Re: Suggestion on index creation for TEXT data field
On 5/21/20 7:27 AM, postgann2020 s wrote: Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contains GIS data and we are trying to creating an index on the column which is having an avg width of 149bytes. CREATE INDEX index_idx ON SCHEMA.TABLE USING btree (column); ERROR: index row size 2976 exceeds maximum 2712 for index "index_idx" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full-text indexing.^^ Hint supplies answer to 1) and 2) below. Could you please suggest on below queries. 1. How to solve the issue?. 2. What type of index is the best suited for this type of data?. Thanks for your support. Regards, PostgAnn. -- Adrian Klaver [email protected]
Re: Suggestion on table analyze
Hi Adrian,
Thanks, I'll check it out.
Regards,
PostgAnn.
On Thu, May 21, 2020 at 8:11 PM Adrian Klaver
wrote:
> On 5/21/20 7:18 AM, postgann2020 s wrote:
> > Hi Team,
> >
> > Thanks for your support.
> >
> > Could you please suggest on below query.
> >
> > Environment
> > PostgreSQL: 9.5.15
> > Postgis: 2.2.7
> >
> > Mostly table contain GIS data.
> >
> > While analyzing the table getting below NOTICE. It seems is pretty
> > understanding, but needs help on the below points.
> >
> > 1 . What might be the reason for getting the NOTICE?.
> > 2. Is this lead to any problems in the future?.
> >
> > ANALYZE SCHEMA.TABLE;
> >
> > NOTICE: no non-null/empty features, unable to compute statistics
> > NOTICE: no non-null/empty features, unable to compute statistics
> > Query returned successfully with no result in 1.1 secs.
>
> This is coming from PostGIS:
>
> postgis/gserialized_estimate.c:
> /* If there's no useful features, we can't work out stats */
> if ( ! notnull_cnt )
> {
> elog(NOTICE, "no non-null/empty features, unable to
> compute statistics");
> stats->stats_valid = false;
> return;
> }
>
>
>
> You might find more information from here:
>
> https://postgis.net/support/
>
> Though FYI PostGIS 2.2.7 is past EOL:
>
> https://postgis.net/source/
>
> >
> > Thanks for your support.
> >
> > Regards,
> > PostgAnn.
>
>
> --
> Adrian Klaver
> [email protected]
>
Re: Suggestion on index creation for TEXT data field
On Thu, May 21, 2020 at 7:45 AM postgann2020 s wrote: > >And what type of data exactly are we talking about. ==> Column is > stroing GIS data. > GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this is maybe better posted to the PostGIS community directly... David J.
Re: Suggestion on index creation for TEXT data field
Hi David, Adrian, Thanks for the information. Sure, will post on PostGIS community. Regards, PostgAnn. On Thu, May 21, 2020 at 8:21 PM David G. Johnston < [email protected]> wrote: > On Thu, May 21, 2020 at 7:45 AM postgann2020 s > wrote: > >> >And what type of data exactly are we talking about. ==> Column is >> stroing GIS data. >> > > GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this > is maybe better posted to the PostGIS community directly... > > David J. > >
Suggestion to improve query performance of data validation in proc.
Hi Team, Thanks for your support. Could you please suggest on below query. We have multiple long procs that are having 100s of data validations and currently we have written as below. *** if (SELECT 1 FROM SCHEMA.TABLE WHERE column=data AND column=data) then statements etc.. *** Are there any other ways to validate the data, which will help us to improve the performance of the query?. Thanks for your support. Regards, PostgAnn.
