Home > mailing lists

Re: possibilities for SQL optimization - Mailing list pgsql-general

From	Pavel Stehule
Subject	Re: possibilities for SQL optimization
Date	April 16, 2020 17:22:36
Msg-id	CAFj8pRD5ZFFykCv9QfqWJFiZno-qwgB-V72x1FstgFCf8kjgdg@mail.gmail.com Whole thread Raw
In response to	possibilities for SQL optimization (Chris Stephens <cstephens16@gmail.com>)
Responses	Re: possibilities for SQL optimization
List	pgsql-general

Tree view

čt 16. 4. 2020 v 16:08 odesílatel Chris Stephens <cstephens16@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

pgsql-general by date:

From: Chris Stephens
Date: 16 April 2020, 17:08:09
Subject: possibilities for SQL optimization

From: Chris Stephens
Date: 16 April 2020, 17:57:13
Subject: Re: possibilities for SQL optimization

Re: possibilities for SQL optimization - Mailing list pgsql-general

Previous

Next