Re: possibilities for SQL optimization

disastrous :)

Planning Time: 7.569 ms
Execution Time: 316969.474 ms

On Thu, Apr 16, 2020 at 9:23 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> čt 16. 4. 2020 v 16:08 odesílatel Chris Stephens <cstephens16(at)gmail(dot)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(at)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
>>
>