setting graduated symbology for a small postgres point table crashing qgis when field has null values

[damstep]

What is the bug or the crash?

I have a point table view with around 50,000 records and 160 fields in postgres which loads into qgis very quickly. the problem comes when i try to assign a graduated symbology. no matter which Mode I use - fixed interval, jenks etc, when I hit the classify button qgis freezes . If I use a calculated field to remove the nulls (ie case when field is null then .001 else field end ), the classification works immediately.

Steps to reproduce the issue

open/link to a postgres spatial table go to properties/symbology chose graduated symboloy chose a numeric field with some null values to use. chose a mode - i tried either Jenks or equal interval. select classify qgis freezes.

Versions

QGIS version 3.32.3-Lima QGIS code revision 67d46100b5 Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.7.2 PROJ version 9.3.0 EPSG Registry database version v10.094 (2023-08-08) GEOS version 3.12.0-CAPI-1.18.0 SQLite version 3.41.1 PDAL version 2.5.5 PostgreSQL client version 15.2 SpatiaLite version 5.1.0 QWT version 6.1.6 QScintilla2 version 2.13.4 OS version Windows 10 Version 2009

Active Python plugins AusMap 1.1 AutomaticBackup-master 1.0 autoSaver 2.6 coordinate_capture 0.2 DataPlotly 4.0.3 FreehandRasterGeoreferencer 0.8.3 GeoCoding 2.19 GeometryShapes 0.7 geoscience 1.11 GroupStats 2.2.7 ioGAS ioGAS plugin for QGIS ver 3.22 (build 201460) MagneticDeclination 2.3.1 mapswipetool_plugin 1.2 openlog 0.11.1 PluginLoadTimes 4.0.1 qfieldsync v4.6.0 qgeo 2.0 qgis-stereonet 0.3 qgiscloud 3.8.13 quick_map_services 0.19.33 rasterstats 0.4.3 SpreadsheetLayers 2.1.0 SRTM-Downloader 3.2.0 zoom_level 0.1 db_manager 0.1.20 grassprovider 2.12.99 MetaSearch 0.3.6 otbprovider 2.12.99 processing 2.12.99

Supported QGIS version

• [X] I'm running a supported QGIS version according to the roadmap.

New profile

• [ ] I tried with a new QGIS profile

Additional context

No response

[elpaso]

I can't reproduce, but of course I don't have access to your data.

[SrNetoChan]

@damstep can you provide the DDL definition of the table you are using? Also, just to be extra careful, what version of Postgresql and postgis are you using?

[damstep]

i can give you the database as well, it is open file: https://exp-gswa-mdhdb-bkt01.s3.ap-southeast-2.amazonaws.com/may2023_GSWA_flat_pivot.zip my postgres version is; 15 my postgis version is: 3.4

from this i create a bestinholeassay table:

create table BestInHoleAss as SELECT companyholeid,anumber,max(ag_ppm) as Ag,max(al_ppm) as Al,max(al2o_ppm) as Al2O,max(al2o3_ppm) as Al2O3,max(alo_ppm) as Alo,max(as_ppm) as As,max(as2o3_ppm) as As2O3,max(aso_ppm) as Aso,max(au_ppm) as Au,max(b_ppm) as B,max(ba_ppm) as Ba,max(bao_ppm) as Bao,max(be_ppm) as Be,max(bi_ppm) as Bi,max(bi2o3_ppm) as Bi2O3,max(bio_ppm) as Bio,max(br_ppm) as Br,max(c_ppm) as C,max(ca_ppm) as Ca,max(caco3_ppm) as Caco3,max(cao_ppm) as Cao,max(cd_ppm) as Cd,max(ce_ppm) as Ce,max(ceo_ppm) as Ceo,max(ceo2_ppm) as Ceo2,max(cl_ppm) as Cl,max(co_ppm) as Co,max(co2_ppm) as Co2,max(cr_ppm) as Cr,max(cr2o3_ppm) as Cr2O3,max(cro_ppm) as Cro,max(cs_ppm) as Cs,max(cs2o_ppm) as Cs2O,max(cso_ppm) as Cso,max(cu_ppm) as Cu,max(cuo_ppm) as Cuo,max(dy_ppm) as Dy,max(dy2o3_ppm) as Dy2O3,max(er_ppm) as Er,max(er2o3_ppm) as Er2O3,max(eu_ppm) as Eu,max(eu2o3_ppm) as Eu2O3,max(f_ppm) as F,max(fe_ppm) as Fe,max(fe2o3_ppm) as Fe2O3,max(fe2o3t_ppm) as Fe2O3T,max(feo_ppm) as Feo,max(ga_ppm) as Ga,max(ga2o3_ppm) as Ga2O3,max(gd_ppm) as Gd,max(gd2o3_ppm) as Gd2O3,max(ge_ppm) as Ge,max(h2o_ppm) as H2O,max(hf_ppm) as Hf,max(hfo_ppm) as Hfo,max(hg_ppm) as Hg,max(hm_ppm) as Hm,max(ho_ppm) as Ho,max(ho2o3_ppm) as Ho2O3,max(i_ppm) as I,max(in_ppm) as In,max(ir_ppm) as Ir,max(k_ppm) as K,max(k2o_ppm) as K2O,max(ko_ppm) as Ko,max(la_ppm) as La,max(la2o3_ppm) as La2O3,max(lao_ppm) as Lao,max(le_ppm) as Le,max(li_ppm) as Li,max(li2o_ppm) as Li2O,max(loi_ppm) as Loi,max(lu_ppm) as Lu,max(lu2o3_ppm) as Lu2O3,max(mg_ppm) as Mg,max(mgco3_ppm) as Mgco3,max(mgo_ppm) as Mgo,max(mgo2_ppm) as Mgo2,max(mn_ppm) as Mn,max(mno_ppm) as Mno,max(mno2_ppm) as Mno2,max(mo_ppm) as Mo,max(na_ppm) as Na,max(na2o_ppm) as Na2O,max(nao_ppm) as Nao,max(nb_ppm) as Nb,max(nb2o5_ppm) as Nb2O5,max(nbo_ppm) as Nbo,max(nd_ppm) as Nd,max(nd2o3_ppm) as Nd2O3,max(ni_ppm) as Ni,max(nio_ppm) as Nio,max(os_ppm) as Os,max(p_ppm) as P,max(p2o5_ppm) as P2O5,max(pb_ppm) as Pb,max(pb204_ppm) as Pb204,max(pb206_ppm) as Pb206,max(pb207_ppm) as Pb207,max(pb208_ppm) as Pb208,max(pbo_ppm) as Pbo,max(pd_ppm) as Pd,max(po_ppm) as Po,max(pr_ppm) as Pr,max(pr6o11_ppm) as Pr6O11,max(pt_ppm) as Pt,max(rb_ppm) as Rb,max(rb2o_ppm) as Rb2O,max(rbo_ppm) as Rbo,max(re_ppm) as Re,max(rh_ppm) as Rh,max(ru_ppm) as Ru,max(s_ppm) as S,max(sb_ppm) as Sb,max(sb2o3_ppm) as Sb2O3,max(sbo_ppm) as Sbo,max(sc_ppm) as Sc,max(se_ppm) as Se,max(si_ppm) as Si,max(sio_ppm) as Sio,max(sio2_ppm) as Sio2,max(sm_ppm) as Sm,max(sm2o3_ppm) as Sm2O3,max(sn_ppm) as Sn,max(sno_ppm) as Sno,max(sno2_ppm) as Sno2,max(so3_ppm) as So3,max(sr_ppm) as Sr,max(sro_ppm) as Sro,max(ta_ppm) as Ta,max(ta2o5_ppm) as Ta2O5,max(tao_ppm) as Tao,max(tb_ppm) as Tb,max(tb4o7_ppm) as Tb4O7,max(tc_ppm) as Tc,max(te_ppm) as Te,max(th_ppm) as Th,max(tho_ppm) as Tho,max(tho2_ppm) as Tho2,max(ti_ppm) as Ti,max(tio_ppm) as Tio,max(tio2_ppm) as Tio2,max(tl_ppm) as Tl,max(tm_ppm) as Tm,max(tm2o3_ppm) as Tm2O3,max(u_ppm) as U,max(u3o8_ppm) as U3O8,max(v_ppm) as V,max(v2o5_ppm) as V2O5,max(w_ppm) as W,max(wo_ppm) as Wo,max(wo3_ppm) as Wo3,max(y_ppm) as Y,max(y2o3_ppm) as Y2O3,max(yb_ppm) as Yb,max(yb2o3_ppm) as Yb2O3,max(zn_ppm) as Zn,max(zno_ppm) as Zno,max(zr_ppm) as Zr,max(zro2_ppm) as Zro2

from public."gswa_DhAss" group by companyholeid ,anumber;

'then use a view to filter it by geographical area using a polygon:

SELECT c.ogc_fid, c.id, c.holeid, c.companyholeid, c.longitude, c.latitude, c.dataset, c.holetype, c.azimuth, c.dip, c.maxdepth, c.anumber, c.company, c.wkb_geometry, a.ag, a.al, a.al2o, a.al2o3, a.alo, a."as", a.as2o3, a.aso, a.au, a.b, a.ba, a.bao, a.be, a.bi, a.bi2o3, a.bio, a.br, a.c, a.ca, a.caco3, a.cao, a.cd, a.ce, a.ceo, a.ceo2, a.cl, a.co, a.co2, a.cr, a.cr2o3, a.cro, a.cs, a.cs2o, a.cso, a.cu, a.cuo, a.dy, a.dy2o3, a.er, a.er2o3, a.eu, a.eu2o3, a.f, a.fe, a.fe2o3, a.fe2o3t, a.feo, a.ga, a.ga2o3, a.gd, a.gd2o3, a.ge, a.h2o, a.hf, a.hfo, a.hg, a.hm, a.ho, a.ho2o3, a.i, a."in", a.ir, a.k, a.k2o, a.ko, a.la, a.la2o3, a.lao, a.le, a.li, a.li2o, a.loi, a.lu, a.lu2o3, a.mg, a.mgco3, a.mgo, a.mgo2, a.mn, a.mno, a.mno2, a.mo, a.na, a.na2o, a.nao, a.nb, a.nb2o5, a.nbo, a.nd, a.nd2o3, a.ni, a.nio, a.os, a.p, a.p2o5, a.pb, a.pb204, a.pb206, a.pb207, a.pb208, a.pbo, a.pd, a.po, a.pr, a.pr6o11, a.pt, a.rb, a.rb2o, a.rbo, a.re, a.rh, a.ru, a.s, a.sb, a.sb2o3, a.sbo, a.sc, a.se, a.si, a.sio, a.sio2, a.sm, a.sm2o3, a.sn, a.sno, a.sno2, a.so3, a.sr, a.sro, a.ta, a.ta2o5, a.tao, a.tb, a.tb4o7, a.tc, a.te, a.th, a.tho, a.tho2, a.ti, a.tio, a.tio2, a.tl, a.tm, a.tm2o3, a.u, a.u3o8, a.v, a.v2o5, a.w, a.wo, a.wo3, a.y, a.y2o3, a.yb, a.yb2o3, a.zn, a.zno, a.zr, a.zro2 FROM gswa_dh_collar c LEFT JOIN bestinholeass a ON c.companyholeid::text = a.companyholeid::text AND c.anumber = a.anumber JOIN polygons p ON st_coveredby(c.wkb_geometry, st_transform(p.geom, 4283)) WHERE p.region::text ~~ '%S%Pat%'::text;

which I then link to qgis

[SrNetoChan]

How long does that last view take to return results?

It seems you are not using indexes on that spatial condition.

[damstep]

in postgres 3 seconds and similar in qgis there is currently on only one record in the polygon table any suggestion for improving it welcome