disastrous :) Planning Time: 7.569 ms Execution Time: 316969.474 ms
On Thu, Apr 16, 2020 at 9:23 AM Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > čt 16. 4. 2020 v 16:08 odesílatel Chris Stephens <cstephen...@gmail.com> > napsal: > >> PG12 >> RHEL 8 >> >> I suspect there's little I can do to get the following query to run >> faster/more efficiently but thought I'd post to list and confirm. >> >> Caveat: I'm coming from an Oracle background and am extremely wet behind >> ears w/ respect to postgresql (please be kind :)). >> >> Right now, we can't change the software generating the SQL though that is >> very possible/likely in the future. For now, the query is what it is. I can >> alter indexing, add table partitioning, or anything else that doesn't >> change logical structure of table. >> >> This is a very wide table holding astronomical data which will be used to >> track changes in the sky to generate alerts for astronomers to evaluate. >> The query has a variable number of "pixelId BETWEEN" predicates. As data >> volume in DiaSource increases, performance of query decreases. I need to >> confirm this but I suspect the primary reason for the decreased performance >> is increased number of "pixelId BETWEEN" predicates generated by >> application. Predicate count is the only thing that changes. I don't think >> performance would otherwise degrade given execution plan. >> >> [local] xxxx@zzzzz=# \dS+ "DiaSource" >> Table >> "public.DiaSource" >> Column | Type | Collation | >> Nullable | Default | Storage | Stats target | Description >> >> -----------------------------+-----------------------------+-----------+----------+-----------------------+---------+--------------+------------- >> diaSourceId | bigint | | >> not null | '0'::bigint | plain | | >> ccdVisitId | bigint | | >> not null | '0'::bigint | plain | | >> diaObjectId | bigint | | >> | '0'::bigint | plain | | >> ssObjectId | bigint | | >> | '0'::bigint | plain | | >> parentDiaSourceId | bigint | | >> | '0'::bigint | plain | | >> prv_procOrder | integer | | >> not null | 0 | plain | | >> ssObjectReassocTime | timestamp without time zone | | >> | | plain | | >> midPointTai | double precision | | >> not null | '0'::double precision | plain | | >> ra | double precision | | >> not null | '0'::double precision | plain | | >> raSigma | double precision | | >> not null | '0'::double precision | plain | | >> decl | double precision | | >> not null | '0'::double precision | plain | | >> declSigma | double precision | | >> not null | '0'::double precision | plain | | >> ra_decl_Cov | double precision | | >> not null | '0'::double precision | plain | | >> x | double precision | | >> not null | '0'::double precision | plain | | >> xSigma | double precision | | >> not null | '0'::double precision | plain | | >> y | double precision | | >> not null | '0'::double precision | plain | | >> ySigma | double precision | | >> not null | '0'::double precision | plain | | >> x_y_Cov | double precision | | >> not null | '0'::double precision | plain | | >> apFlux | double precision | | >> not null | '0'::double precision | plain | | >> apFluxErr | double precision | | >> not null | '0'::double precision | plain | | >> snr | double precision | | >> not null | '0'::double precision | plain | | >> psFlux | double precision | | >> | '0'::double precision | plain | | >> psFluxSigma | double precision | | >> | '0'::double precision | plain | | >> psRa | double precision | | >> | '0'::double precision | plain | | >> psRaSigma | double precision | | >> | '0'::double precision | plain | | >> psDecl | double precision | | >> | '0'::double precision | plain | | >> psDeclSigma | double precision | | >> | '0'::double precision | plain | | >> psFlux_psRa_Cov | double precision | | >> | '0'::double precision | plain | | >> psFlux_psDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> psRa_psDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> psLnL | double precision | | >> | '0'::double precision | plain | | >> psChi2 | double precision | | >> | '0'::double precision | plain | | >> psNdata | integer | | >> | 0 | plain | | >> trailFlux | double precision | | >> | '0'::double precision | plain | | >> trailFluxSigma | double precision | | >> | '0'::double precision | plain | | >> trailRa | double precision | | >> | '0'::double precision | plain | | >> trailRaSigma | double precision | | >> | '0'::double precision | plain | | >> trailDecl | double precision | | >> | '0'::double precision | plain | | >> trailDeclSigma | double precision | | >> | '0'::double precision | plain | | >> trailLength | double precision | | >> | '0'::double precision | plain | | >> trailLengthSigma | double precision | | >> | '0'::double precision | plain | | >> trailAngle | double precision | | >> | '0'::double precision | plain | | >> trailAngleSigma | double precision | | >> | '0'::double precision | plain | | >> trailFlux_trailRa_Cov | double precision | | >> | '0'::double precision | plain | | >> trailFlux_trailDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> trailFlux_trailLength_Cov | double precision | | >> | '0'::double precision | plain | | >> trailFlux_trailAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> trailRa_trailDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> trailRa_trailLength_Cov | double precision | | >> | '0'::double precision | plain | | >> trailRa_trailAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> trailDecl_trailLength_Cov | double precision | | >> | '0'::double precision | plain | | >> trailDecl_trailAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> trailLength_trailAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> trailLnL | double precision | | >> | '0'::double precision | plain | | >> trailChi2 | double precision | | >> | '0'::double precision | plain | | >> trailNdata | integer | | >> | 0 | plain | | >> dipMeanFlux | double precision | | >> | '0'::double precision | plain | | >> dipMeanFluxSigma | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiff | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiffSigma | double precision | | >> | '0'::double precision | plain | | >> dipRa | double precision | | >> | '0'::double precision | plain | | >> dipRaSigma | double precision | | >> | '0'::double precision | plain | | >> dipDecl | double precision | | >> | '0'::double precision | plain | | >> dipDeclSigma | double precision | | >> | '0'::double precision | plain | | >> dipLength | double precision | | >> | '0'::double precision | plain | | >> dipLengthSigma | double precision | | >> | '0'::double precision | plain | | >> dipAngle | double precision | | >> | '0'::double precision | plain | | >> dipAngleSigma | double precision | | >> | '0'::double precision | plain | | >> dipMeanFlux_dipFluxDiff_Cov | double precision | | >> | '0'::double precision | plain | | >> dipMeanFlux_dipRa_Cov | double precision | | >> | '0'::double precision | plain | | >> dipMeanFlux_dipDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> dipMeanFlux_dipLength_Cov | double precision | | >> | '0'::double precision | plain | | >> dipMeanFlux_dipAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiff_dipRa_Cov | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiff_dipDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiff_dipLength_Cov | double precision | | >> | '0'::double precision | plain | | >> dipFluxDiff_dipAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> dipRa_dipDecl_Cov | double precision | | >> | '0'::double precision | plain | | >> dipRa_dipLength_Cov | double precision | | >> | '0'::double precision | plain | | >> dipRa_dipAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> dipDecl_dipLength_Cov | double precision | | >> | '0'::double precision | plain | | >> dipDecl_dipAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> dipLength_dipAngle_Cov | double precision | | >> | '0'::double precision | plain | | >> dipLnL | double precision | | >> | '0'::double precision | plain | | >> dipChi2 | double precision | | >> | '0'::double precision | plain | | >> dipNdata | integer | | >> | 0 | plain | | >> totFlux | double precision | | >> | '0'::double precision | plain | | >> totFluxErr | double precision | | >> | '0'::double precision | plain | | >> diffFlux | double precision | | >> | '0'::double precision | plain | | >> diffFluxErr | double precision | | >> | '0'::double precision | plain | | >> fpBkgd | double precision | | >> | '0'::double precision | plain | | >> fpBkgdErr | double precision | | >> | '0'::double precision | plain | | >> ixx | double precision | | >> | '0'::double precision | plain | | >> ixxSigma | double precision | | >> | '0'::double precision | plain | | >> iyy | double precision | | >> | '0'::double precision | plain | | >> iyySigma | double precision | | >> | '0'::double precision | plain | | >> ixy | double precision | | >> | '0'::double precision | plain | | >> ixySigma | double precision | | >> | '0'::double precision | plain | | >> ixx_iyy_Cov | double precision | | >> | '0'::double precision | plain | | >> ixx_ixy_Cov | double precision | | >> | '0'::double precision | plain | | >> iyy_ixy_Cov | double precision | | >> | '0'::double precision | plain | | >> ixxPSF | double precision | | >> | '0'::double precision | plain | | >> iyyPSF | double precision | | >> | '0'::double precision | plain | | >> ixyPSF | double precision | | >> | '0'::double precision | plain | | >> extendedness | double precision | | >> | '0'::double precision | plain | | >> spuriousness | double precision | | >> | '0'::double precision | plain | | >> flags | bigint | | >> not null | '0'::bigint | plain | | >> pixelId | bigint | | >> not null | '0'::bigint | plain | | >> Indexes: >> "PK_DiaSource" PRIMARY KEY, btree ("diaSourceId") >> "IDX_DiaSource_ccdVisitId" btree ("ccdVisitId") >> "IDX_DiaSource_diaObjId" btree ("diaObjectId") >> "IDX_DiaSource_htmId20" btree ("pixelId") >> "IDX_DiaSource_ssObjId" btree ("ssObjectId") >> Access method: heap >> >> explain analyze SELECT "DiaSource"."diaSourceId", >> "DiaSource"."ccdVisitId", "DiaSource"."diaObjectId", >> "DiaSource"."ssObjectId", "DiaSource"."parentDiaSourceId", >> "DiaSource"."prv_procOrder", "DiaSource"."ssObjectReassocTime", >> "DiaSource"."midPointTai", "DiaSource".ra, "DiaSource"."raSigma", >> "DiaSource".decl, "DiaSource"."declSigma", "DiaSource"."ra_decl_Cov", >> "DiaSource".x, "DiaSource"."xSigma", "DiaSource".y, "DiaSource"."ySigma", >> "DiaSource"."x_y_Cov", "DiaSource"."apFlux", "DiaSource"."apFluxErr", >> "DiaSource".snr, "DiaSource"."psFlux", "DiaSource"."psFluxSigma", >> "DiaSource"."psRa", "DiaSource"."psRaSigma", "DiaSource"."psDecl", >> "DiaSource"."psDeclSigma", "DiaSource"."psFlux_psRa_Cov", >> "DiaSource"."psFlux_psDecl_Cov", "DiaSource"."psRa_psDecl_Cov", >> "DiaSource"."psLnL", "DiaSource"."psChi2", "DiaSource"."psNdata", >> "DiaSource"."trailFlux", "DiaSource"."trailFluxSigma", >> "DiaSource"."trailRa", "DiaSource"."trailRaSigma", "DiaSource"."trailDecl", >> "DiaSource"."trailDeclSigma", "DiaSource"."trailLength", >> "DiaSource"."trailLengthSigma", "DiaSource"."trailAngle", >> "DiaSource"."trailAngleSigma", "DiaSource"."trailFlux_trailRa_Cov", >> "DiaSource"."trailFlux_trailDecl_Cov", >> "DiaSource"."trailFlux_trailLength_Cov", >> "DiaSource"."trailFlux_trailAngle_Cov", >> "DiaSource"."trailRa_trailDecl_Cov", "DiaSource"."trailRa_trailLength_Cov", >> "DiaSource"."trailRa_trailAngle_Cov", >> "DiaSource"."trailDecl_trailLength_Cov", >> "DiaSource"."trailDecl_trailAngle_Cov", >> "DiaSource"."trailLength_trailAngle_Cov", "DiaSource"."trailLnL", >> "DiaSource"."trailChi2", "DiaSource"."trailNdata", >> "DiaSource"."dipMeanFlux", "DiaSource"."dipMeanFluxSigma", >> "DiaSource"."dipFluxDiff", "DiaSource"."dipFluxDiffSigma", >> "DiaSource"."dipRa", "DiaSource"."dipRaSigma", "DiaSource"."dipDecl", >> "DiaSource"."dipDeclSigma", "DiaSource"."dipLength", >> "DiaSource"."dipLengthSigma", "DiaSource"."dipAngle", >> "DiaSource"."dipAngleSigma", "DiaSource"."dipMeanFlux_dipFluxDiff_Cov", >> "DiaSource"."dipMeanFlux_dipRa_Cov", "DiaSource"."dipMeanFlux_dipDecl_Cov", >> "DiaSource"."dipMeanFlux_dipLength_Cov", >> "DiaSource"."dipMeanFlux_dipAngle_Cov", >> "DiaSource"."dipFluxDiff_dipRa_Cov", "DiaSource"."dipFluxDiff_dipDecl_Cov", >> "DiaSource"."dipFluxDiff_dipLength_Cov", >> "DiaSource"."dipFluxDiff_dipAngle_Cov", "DiaSource"."dipRa_dipDecl_Cov", >> "DiaSource"."dipRa_dipLength_Cov", "DiaSource"."dipRa_dipAngle_Cov", >> "DiaSource"."dipDecl_dipLength_Cov", "DiaSource"."dipDecl_dipAngle_Cov", >> "DiaSource"."dipLength_dipAngle_Cov", "DiaSource"."dipLnL", >> "DiaSource"."dipChi2", "DiaSource"."dipNdata", "DiaSource"."totFlux", >> "DiaSource"."totFluxErr", "DiaSource"."diffFlux", >> "DiaSource"."diffFluxErr", "DiaSource"."fpBkgd", "DiaSource"."fpBkgdErr", >> "DiaSource".ixx, "DiaSource"."ixxSigma", "DiaSource".iyy, >> "DiaSource"."iyySigma", "DiaSource".ixy, "DiaSource"."ixySigma", >> "DiaSource"."ixx_iyy_Cov", "DiaSource"."ixx_ixy_Cov", >> "DiaSource"."iyy_ixy_Cov", "DiaSource"."ixxPSF", "DiaSource"."iyyPSF", >> "DiaSource"."ixyPSF", "DiaSource".extendedness, "DiaSource".spuriousness, >> "DiaSource".flags, "DiaSource"."pixelId" FROM "DiaSource" WHERE >> "DiaSource"."pixelId" BETWEEN 10729436479488 AND 10729440673791 OR >> "DiaSource"."pixelId" BETWEEN 10729449062400 AND 10729499394047 OR >> "DiaSource"."pixelId" BETWEEN 10729532948480 AND 10729537142783 OR >> "DiaSource"."pixelId" BETWEEN 10729542385664 AND 10729542451199 OR >> "DiaSource"."pixelId" BETWEEN 10729542516736 AND 10729542647807 OR >> "DiaSource"."pixelId" BETWEEN 10729546579968 AND 10729546711039 OR >> "DiaSource"."pixelId" BETWEEN 10729546776576 AND 10729546842111 OR >> "DiaSource"."pixelId" BETWEEN 10729566502912 AND 10729570697215 OR >> "DiaSource"."pixelId" BETWEEN 10729570959360 AND 10729571090431 OR >> "DiaSource"."pixelId" BETWEEN 10729571155968 AND 10729571221503 OR >> "DiaSource"."pixelId" BETWEEN 10729571745792 AND 10729573056511 OR >> "DiaSource"."pixelId" BETWEEN 10729573122048 AND 10729574105087 OR >> "DiaSource"."pixelId" BETWEEN 10729574170624 AND 10729583280127 OR >> "DiaSource"."pixelId" BETWEEN 10729601105920 AND 10729601171455 OR >> "DiaSource"."pixelId" BETWEEN 10729604251648 AND 10729608445951 OR >> "DiaSource"."pixelId" BETWEEN 10729613164544 AND 10729613295615 OR >> "DiaSource"."pixelId" BETWEEN 10729613361152 AND 10729613426687 OR >> "DiaSource"."pixelId" BETWEEN 10729614737408 AND 10729614868479 OR >> "DiaSource"."pixelId" BETWEEN 10729614934016 AND 10729615065087 OR >> "DiaSource"."pixelId" BETWEEN 10729615130624 AND 10729615261695 OR >> "DiaSource"."pixelId" BETWEEN 10729615654912 AND 10729615720447 OR >> "DiaSource"."pixelId" BETWEEN 10729616048128 AND 10729616113663 OR >> "DiaSource"."pixelId" BETWEEN 10729621028864 AND 10729625223167 OR >> "DiaSource"."pixelId" BETWEEN 10729625747456 AND 10729625812991 OR >> "DiaSource"."pixelId" BETWEEN 10729625878528 AND 10729626009599 OR >> "DiaSource"."pixelId" BETWEEN 10729626337280 AND 10729626796031 OR >> "DiaSource"."pixelId" BETWEEN 10729626861568 AND 10729628893183 OR >> "DiaSource"."pixelId" BETWEEN 10729628958720 AND 10729630466047 OR >> "DiaSource"."pixelId" BETWEEN 10729630597120 AND 10729630662655 OR >> "DiaSource"."pixelId" BETWEEN 10729630793728 AND 10729630859263 OR >> "DiaSource"."pixelId" BETWEEN 10729630990336 AND 10729631383551 OR >> "DiaSource"."pixelId" BETWEEN 10729631449088 AND 10729633611775 OR >> "DiaSource"."pixelId" BETWEEN 10729650388992 AND 10729650651135 OR >> "DiaSource"."pixelId" BETWEEN 10729650716672 AND 10729651437567 OR >> "DiaSource"."pixelId" BETWEEN 10729652748288 AND 10729653010431 OR >> "DiaSource"."pixelId" BETWEEN 10729653141504 AND 10729653207039 OR >> "DiaSource"."pixelId" BETWEEN 10729653272576 AND 10729653338111 OR >> "DiaSource"."pixelId" BETWEEN 10729653600256 AND 10729653665791 OR >> "DiaSource"."pixelId" BETWEEN 10729653796864 AND 10729654059007 OR >> "DiaSource"."pixelId" BETWEEN 10729654190080 AND 10729654255615 OR >> "DiaSource"."pixelId" BETWEEN 10729654321152 AND 10729654386687 OR >> "DiaSource"."pixelId" BETWEEN 10729654452224 AND 10729654583295 OR >> "DiaSource"."pixelId" BETWEEN 10729734275072 AND 10729734799359 OR >> "DiaSource"."pixelId" BETWEEN 10729735061504 AND 10729735585791 OR >> "DiaSource"."pixelId" BETWEEN 10729735847936 AND 10729736372223 OR >> "DiaSource"."pixelId" BETWEEN 10729737945088 AND 10729738207231 OR >> "DiaSource"."pixelId" BETWEEN 10729738993664 AND 10729739124735 OR >> "DiaSource"."pixelId" BETWEEN 10729739190272 AND 10729739255807 OR >> "DiaSource"."pixelId" BETWEEN 10729740566528 AND 10729740697599 OR >> "DiaSource"."pixelId" BETWEEN 10729740763136 AND 10729740894207 OR >> "DiaSource"."pixelId" BETWEEN 10729740959744 AND 10729741090815 OR >> "DiaSource"."pixelId" BETWEEN 10729741484032 AND 10729741549567 OR >> "DiaSource"."pixelId" BETWEEN 10729741877248 AND 10729741942783 OR >> "DiaSource"."pixelId" BETWEEN 10729748955136 AND 10729749020671 OR >> "DiaSource"."pixelId" BETWEEN 10729868492800 AND 10729868754943 OR >> "DiaSource"."pixelId" BETWEEN 10729869082624 AND 10729869148159 OR >> "DiaSource"."pixelId" BETWEEN 10729869344768 AND 10729869410303 OR >> "DiaSource"."pixelId" BETWEEN 10776494538752 AND 10776494604287; >> >> Bitmap Heap Scan on "DiaSource" (cost=541.17..33856.16 rows=7831 >> width=848) (actual time=2.108..39.744 rows=8524 loops=1) >> Recheck Cond: ((("pixelId" >= '10729436479488'::bigint) AND ("pixelId" >> <= '10729440673791'::bigint)) OR (("pixelId" >= '10729449062400'::bigint) >> AND ("pixelId" <= '10729499394047'::bigint)) OR (("pixelId" >= >> '10729532948480'::bigint) AND ("pixelId" <= '10729537142783'::bigint)) >> OR (("pixelId" >= '10729542385664'::bigint) AND ("pixelId" <= >> '10729542451199'::bigint)) OR (("pixelId" >= '10729542516736'::bigint) AND ( >> "pixelId" <= '10729542647807'::bigint)) OR (("pixelId" >= >> '10729546579968'::bigint) AND ("pixelId" <= '10729546711039'::bigint)) OR >> (("pixelId" >= '10729546776576'::bigint) AND ("pixelId" <= >> '10729546842111'::b >> igint)) OR (("pixelId" >= '10729566502912'::bigint) AND ("pixelId" <= >> '10729570697215'::bigint)) OR (("pixelId" >= '10729570959360'::bigint) AND >> ("pixelId" <= '10729571090431'::bigint)) OR (("pixelId" >= '10729 >> 571155968'::bigint) AND ("pixelId" <= '10729571221503'::bigint)) OR >> (("pixelId" >= '10729571745792'::bigint) AND ("pixelId" <= >> '10729573056511'::bigint)) OR (("pixelId" >= '10729573122048'::bigint) AND >> ("pixelI >> d" <= '10729574105087'::bigint)) OR (("pixelId" >= >> '10729574170624'::bigint) AND ("pixelId" <= '10729583280127'::bigint)) OR >> (("pixelId" >= '10729601105920'::bigint) AND ("pixelId" <= >> '10729601171455'::bigint)) >> OR (("pixelId" >= '10729604251648'::bigint) AND ("pixelId" <= >> '10729608445951'::bigint)) OR (("pixelId" >= '10729613164544'::bigint) AND >> ("pixelId" <= '10729613295615'::bigint)) OR (("pixelId" >= '107296133611 >> 52'::bigint) AND ("pixelId" <= '10729613426687'::bigint)) OR (("pixelId" >> >= '10729614737408'::bigint) AND ("pixelId" <= '10729614868479'::bigint)) >> OR (("pixelId" >= '10729614934016'::bigint) AND ("pixelId" <= ' >> 10729615065087'::bigint)) OR (("pixelId" >= '10729615130624'::bigint) AND >> ("pixelId" <= '10729615261695'::bigint)) OR (("pixelId" >= >> '10729615654912'::bigint) AND ("pixelId" <= '10729615720447'::bigint)) OR >> ((" >> pixelId" >= '10729616048128'::bigint) AND ("pixelId" <= >> '10729616113663'::bigint)) OR (("pixelId" >= '10729621028864'::bigint) AND >> ("pixelId" <= '10729625223167'::bigint)) OR (("pixelId" >= >> '10729625747456'::bi >> gint) AND ("pixelId" <= '10729625812991'::bigint)) OR (("pixelId" >= >> '10729625878528'::bigint) AND ("pixelId" <= '10729626009599'::bigint)) OR >> (("pixelId" >= '10729626337280'::bigint) AND ("pixelId" <= '1072962 >> 6796031'::bigint)) OR (("pixelId" >= '10729626861568'::bigint) AND >> ("pixelId" <= '10729628893183'::bigint)) OR (("pixelId" >= >> '10729628958720'::bigint) AND ("pixelId" <= '10729630466047'::bigint)) OR >> (("pixelId >> " >= '10729630597120'::bigint) AND ("pixelId" <= >> '10729630662655'::bigint)) OR (("pixelId" >= '10729630793728'::bigint) AND >> ("pixelId" <= '10729630859263'::bigint)) OR (("pixelId" >= >> '10729630990336'::bigint) A >> ND ("pixelId" <= '10729631383551'::bigint)) OR (("pixelId" >= >> '10729631449088'::bigint) AND ("pixelId" <= '10729633611775'::bigint)) OR >> (("pixelId" >= '10729650388992'::bigint) AND ("pixelId" <= '10729650651135 >> '::bigint)) OR (("pixelId" >= '10729650716672'::bigint) AND ("pixelId" <= >> '10729651437567'::bigint)) OR (("pixelId" >= '10729652748288'::bigint) AND >> ("pixelId" <= '10729653010431'::bigint)) OR (("pixelId" >= '1 >> 0729653141504'::bigint) AND ("pixelId" <= '10729653207039'::bigint)) OR >> (("pixelId" >= '10729653272576'::bigint) AND ("pixelId" <= >> '10729653338111'::bigint)) OR (("pixelId" >= '10729653600256'::bigint) AND >> ("pi >> xelId" <= '10729653665791'::bigint)) OR (("pixelId" >= >> '10729653796864'::bigint) AND ("pixelId" <= '10729654059007'::bigint)) OR >> (("pixelId" >= '10729654190080'::bigint) AND ("pixelId" <= >> '10729654255615'::bigi >> nt)) OR (("pixelId" >= '10729654321152'::bigint) AND ("pixelId" <= >> '10729654386687'::bigint)) OR (("pixelId" >= '10729654452224'::bigint) AND >> ("pixelId" <= '10729654583295'::bigint)) OR (("pixelId" >= '10729734 >> 275072'::bigint) AND ("pixelId" <= '10729734799359'::bigint)) OR >> (("pixelId" >= '10729735061504'::bigint) AND ("pixelId" <= >> '10729735585791'::bigint)) OR (("pixelId" >= '10729735847936'::bigint) AND >> ("pixelId" >> <= '10729736372223'::bigint)) OR (("pixelId" >= '10729737945088'::bigint) >> AND ("pixelId" <= '10729738207231'::bigint)) OR (("pixelId" >= >> '10729738993664'::bigint) AND ("pixelId" <= '10729739124735'::bigint)) OR >> (("pixelId" >= '10729739190272'::bigint) AND ("pixelId" <= >> '10729739255807'::bigint)) OR (("pixelId" >= '10729740566528'::bigint) AND >> ("pixelId" <= '10729740697599'::bigint)) OR (("pixelId" >= '10729740763136' >> ::bigint) AND ("pixelId" <= '10729740894207'::bigint)) OR (("pixelId" >= >> '10729740959744'::bigint) AND ("pixelId" <= '10729741090815'::bigint)) OR >> (("pixelId" >= '10729741484032'::bigint) AND ("pixelId" <= '107 >> 29741549567'::bigint)) OR (("pixelId" >= '10729741877248'::bigint) AND >> ("pixelId" <= '10729741942783'::bigint)) OR (("pixelId" >= >> '10729748955136'::bigint) AND ("pixelId" <= '10729749020671'::bigint)) OR >> (("pix >> elId" >= '10729868492800'::bigint) AND ("pixelId" <= >> '10729868754943'::bigint)) OR (("pixelId" >= '10729869082624'::bigint) AND >> ("pixelId" <= '10729869148159'::bigint)) OR (("pixelId" >= >> '10729869344768'::bigin >> t) AND ("pixelId" <= '10729869410303'::bigint)) OR (("pixelId" >= >> '10776494538752'::bigint) AND ("pixelId" <= '10776494604287'::bigint))) >> Heap Blocks: exact=2377 >> -> BitmapOr (cost=541.17..541.17 rows=7831 width=0) (actual >> time=1.699..1.699 rows=0 loops=1) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..11.92 rows=335 width=0) (actual time=0.137..0.137 rows=406 >> loops=1) >> Index Cond: (("pixelId" >= '10729436479488'::bigint) AND >> ("pixelId" <= '10729440673791'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..100.33 rows=3976 width=0) (actual time=0.810..0.810 rows=4886 >> loops=1) >> Index Cond: (("pixelId" >= '10729449062400'::bigint) AND >> ("pixelId" <= '10729499394047'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..11.92 rows=335 width=0) (actual time=0.043..0.043 rows=333 >> loops=1) >> Index Cond: (("pixelId" >= '10729532948480'::bigint) AND >> ("pixelId" <= '10729537142783'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.008..0.008 rows=1 loops=1) >> Index Cond: (("pixelId" >= '10729542385664'::bigint) AND >> ("pixelId" <= '10729542451199'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.003..0.003 rows=16 >> loops=1) >> Index Cond: (("pixelId" >= '10729542516736'::bigint) AND >> ("pixelId" <= '10729542647807'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.008..0.008 rows=5 loops=1) >> Index Cond: (("pixelId" >= '10729546579968'::bigint) AND >> ("pixelId" <= '10729546711039'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=8 loops=1) >> Index Cond: (("pixelId" >= '10729546776576'::bigint) AND >> ("pixelId" <= '10729546842111'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..11.92 rows=335 width=0) (actual time=0.035..0.035 rows=318 >> loops=1) >> Index Cond: (("pixelId" >= '10729566502912'::bigint) AND >> ("pixelId" <= '10729570697215'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.002..0.002 rows=0 loops=1) >> Index Cond: (("pixelId" >= '10729570959360'::bigint) AND >> ("pixelId" <= '10729571090431'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.002..0.003 rows=8 loops=1) >> Index Cond: (("pixelId" >= '10729571155968'::bigint) AND >> ("pixelId" <= '10729571221503'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.65 rows=108 width=0) (actual time=0.018..0.018 rows=108 >> loops=1) >> Index Cond: (("pixelId" >= '10729571745792'::bigint) AND >> ("pixelId" <= '10729573056511'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.39 rows=82 width=0) (actual time=0.010..0.010 rows=105 >> loops=1) >> Index Cond: (("pixelId" >= '10729573122048'::bigint) AND >> ("pixelId" <= '10729574105087'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..19.80 rows=723 width=0) (actual time=0.063..0.063 rows=674 >> loops=1) >> Index Cond: (("pixelId" >= '10729574170624'::bigint) AND >> ("pixelId" <= '10729583280127'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.008..0.008 rows=1 loops=1) >> Index Cond: (("pixelId" >= '10729601105920'::bigint) AND >> ("pixelId" <= '10729601171455'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..11.92 rows=335 width=0) (actual time=0.074..0.074 rows=343 >> loops=1) >> Index Cond: (("pixelId" >= '10729604251648'::bigint) AND >> ("pixelId" <= '10729608445951'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.009..0.009 rows=16 >> loops=1) >> Index Cond: (("pixelId" >= '10729613164544'::bigint) AND >> ("pixelId" <= '10729613295615'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729613361152'::bigint) AND >> ("pixelId" <= '10729613426687'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.009..0.009 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729614737408'::bigint) AND >> ("pixelId" <= '10729614868479'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.003..0.003 rows=12 >> loops=1) >> Index Cond: (("pixelId" >= '10729614934016'::bigint) AND >> ("pixelId" <= '10729615065087'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.003..0.003 rows=11 >> loops=1) >> Index Cond: (("pixelId" >= '10729615130624'::bigint) AND >> ("pixelId" <= '10729615261695'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=10 >> loops=1) >> Index Cond: (("pixelId" >= '10729615654912'::bigint) AND >> ("pixelId" <= '10729615720447'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729616048128'::bigint) AND >> ("pixelId" <= '10729616113663'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..11.92 rows=335 width=0) (actual time=0.036..0.036 rows=254 >> loops=1) >> Index Cond: (("pixelId" >= '10729621028864'::bigint) AND >> ("pixelId" <= '10729625223167'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.009..0.009 rows=15 >> loops=1) >> Index Cond: (("pixelId" >= '10729625747456'::bigint) AND >> ("pixelId" <= '10729625812991'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.003..0.003 rows=17 >> loops=1) >> Index Cond: (("pixelId" >= '10729625878528'::bigint) AND >> ("pixelId" <= '10729626009599'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.98 rows=41 width=0) (actual time=0.004..0.004 rows=32 >> loops=1) >> Index Cond: (("pixelId" >= '10729626337280'::bigint) AND >> ("pixelId" <= '10729626796031'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..6.22 rows=165 width=0) (actual time=0.029..0.029 rows=144 >> loops=1) >> Index Cond: (("pixelId" >= '10729626861568'::bigint) AND >> ("pixelId" <= '10729628893183'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.80 rows=123 width=0) (actual time=0.016..0.016 rows=100 >> loops=1) >> Index Cond: (("pixelId" >= '10729628958720'::bigint) AND >> ("pixelId" <= '10729630466047'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.005..0.005 rows=4 loops=1) >> Index Cond: (("pixelId" >= '10729630597120'::bigint) AND >> ("pixelId" <= '10729630662655'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729630793728'::bigint) AND >> ("pixelId" <= '10729630859263'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.92 rows=35 width=0) (actual time=0.006..0.006 rows=22 >> loops=1) >> Index Cond: (("pixelId" >= '10729630990336'::bigint) AND >> ("pixelId" <= '10729631383551'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..6.32 rows=175 width=0) (actual time=0.031..0.031 rows=155 >> loops=1) >> Index Cond: (("pixelId" >= '10729631449088'::bigint) AND >> ("pixelId" <= '10729633611775'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.82 rows=25 width=0) (actual time=0.022..0.022 rows=58 >> loops=1) >> Index Cond: (("pixelId" >= '10729650388992'::bigint) AND >> ("pixelId" <= '10729650651135'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.18 rows=61 width=0) (actual time=0.009..0.009 rows=63 >> loops=1) >> Index Cond: (("pixelId" >= '10729650716672'::bigint) AND >> ("pixelId" <= '10729651437567'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.82 rows=25 width=0) (actual time=0.019..0.019 rows=30 >> loops=1) >> Index Cond: (("pixelId" >= '10729652748288'::bigint) AND >> ("pixelId" <= '10729653010431'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.006..0.006 rows=10 >> loops=1) >> Index Cond: (("pixelId" >= '10729653141504'::bigint) AND >> ("pixelId" <= '10729653207039'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.004..0.004 rows=1 loops=1) >> Index Cond: (("pixelId" >= '10729653272576'::bigint) AND >> ("pixelId" <= '10729653338111'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=0 loops=1) >> Index Cond: (("pixelId" >= '10729653600256'::bigint) AND >> ("pixelId" <= '10729653665791'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.82 rows=25 width=0) (actual time=0.006..0.006 rows=20 >> loops=1) >> Index Cond: (("pixelId" >= '10729653796864'::bigint) AND >> ("pixelId" <= '10729654059007'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=1 loops=1) >> Index Cond: (("pixelId" >= '10729654190080'::bigint) AND >> ("pixelId" <= '10729654255615'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.003..0.003 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729654321152'::bigint) AND >> ("pixelId" <= '10729654386687'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.003..0.003 rows=2 loops=1) >> Index Cond: (("pixelId" >= '10729654452224'::bigint) AND >> ("pixelId" <= '10729654583295'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.03 rows=46 width=0) (actual time=0.026..0.026 rows=47 >> loops=1) >> Index Cond: (("pixelId" >= '10729734275072'::bigint) AND >> ("pixelId" <= '10729734799359'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.03 rows=46 width=0) (actual time=0.009..0.009 rows=45 >> loops=1) >> Index Cond: (("pixelId" >= '10729735061504'::bigint) AND >> ("pixelId" <= '10729735585791'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..5.03 rows=46 width=0) (actual time=0.018..0.019 rows=54 >> loops=1) >> Index Cond: (("pixelId" >= '10729735847936'::bigint) AND >> ("pixelId" <= '10729736372223'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.82 rows=25 width=0) (actual time=0.039..0.039 rows=42 >> loops=1) >> Index Cond: (("pixelId" >= '10729737945088'::bigint) AND >> ("pixelId" <= '10729738207231'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.006..0.006 rows=8 loops=1) >> Index Cond: (("pixelId" >= '10729738993664'::bigint) AND >> ("pixelId" <= '10729739124735'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.006..0.006 rows=20 >> loops=1) >> Index Cond: (("pixelId" >= '10729739190272'::bigint) AND >> ("pixelId" <= '10729739255807'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.004..0.004 rows=4 loops=1) >> Index Cond: (("pixelId" >= '10729740566528'::bigint) AND >> ("pixelId" <= '10729740697599'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.005..0.005 rows=16 >> loops=1) >> Index Cond: (("pixelId" >= '10729740763136'::bigint) AND >> ("pixelId" <= '10729740894207'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.72 rows=15 width=0) (actual time=0.005..0.005 rows=13 >> loops=1) >> Index Cond: (("pixelId" >= '10729740959744'::bigint) AND >> ("pixelId" <= '10729741090815'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.020..0.020 rows=23 >> loops=1) >> Index Cond: (("pixelId" >= '10729741484032'::bigint) AND >> ("pixelId" <= '10729741549567'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.005..0.005 rows=3 loops=1) >> Index Cond: (("pixelId" >= '10729741877248'::bigint) AND >> ("pixelId" <= '10729741942783'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.012..0.012 rows=11 >> loops=1) >> Index Cond: (("pixelId" >= '10729748955136'::bigint) AND >> ("pixelId" <= '10729749020671'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.82 rows=25 width=0) (actual time=0.013..0.013 rows=15 >> loops=1) >> Index Cond: (("pixelId" >= '10729868492800'::bigint) AND >> ("pixelId" <= '10729868754943'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.006..0.006 rows=11 >> loops=1) >> Index Cond: (("pixelId" >= '10729869082624'::bigint) AND >> ("pixelId" <= '10729869148159'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.004..0.004 rows=1 loops=1) >> Index Cond: (("pixelId" >= '10729869344768'::bigint) AND >> ("pixelId" <= '10729869410303'::bigint)) >> -> Bitmap Index Scan on "IDX_DiaSource_htmId20" >> (cost=0.00..4.67 rows=10 width=0) (actual time=0.040..0.040 rows=12 >> loops=1) >> Index Cond: (("pixelId" >= '10776494538752'::bigint) AND >> ("pixelId" <= '10776494604287'::bigint)) >> First execution: >> Planning Time: 4.742 ms >> Execution Time: 272.052 ms >> Subsequent executions: >> Planning Time: 1.070 ms >> Execution Time: 40.963 ms >> >> select count(*) from "DiaSource" >> [more] - > ; >> count >> ----------- >> 302971183 >> >> So PG is taking each predicate -> searching through IDX_DiaSource_htmId20 >> -> building bitmap of ctid's (i think that's corollary to oracle's rowid) >> -> visiting table blocks containing any of those pixelID ranges 1x for >> additional columns. That seems extremely efficient to me. >> >> I did try a "create table x as select * from diasource" sorting the table >> by pixelID and creating a brin index to see how that went. Not well (~2.5 >> second response time). My other thought was to range partition by pixelID + >> brin index. I might also play w/ parallel query but process communication >> overhead and scalability implications (100's of concurrent processes >> running similar SQL) probably make that a dead end. >> >> Anyways, I figured I'd submit this to the list first to see if there >> might be better possibilities. >> > > It is hard to do some better - just you can check if without bitmap index > scan this query will be better > > set enable_bitmapscan to off'; > > and repeat EXPLAIN ANALYZE .. > > Regards > > Pavel > > >> Thanks for any input! >> >> Chris >> >