č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
>
