Hello,
I've a basic table with about 100K rows:
CREATE TABLE "public"."push_topic" (
"id" Serial PRIMARY KEY,
"guid" public.push_guid NOT NULL,
"authenticatorsending" Varchar(32) NOT NULL,
"authenticatorsubscription" Varchar(32) NOT NULL,
"countpushed" Integer NOT NULL,
"datecreated" timestamp NOT NULL,
"datelastpush" timestamp
)
CREATE UNIQUE INDEX push_topic_idx_topicguid ON push_topic
USING btree (guid)
When I query this through pgsql, the queries are fast as expected.
This is the query:
select * from push_topic where guid = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'
And the plan:
Index Scan using push_topic_idx_topicguid on push_topic (cost=0.42..8.44
rows=1 width=103) (actual time=0.117..0.121 rows=1 loops=1)
Index Cond: ((guid)::bpchar = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar)
Buffers: shared hit=3 read=1
Total runtime: 0.191 ms
However when I run the exact query through a different application
(CodeSynthesis ORM) the query is very slow (~ 115ms logged)
I noted this is due to a sequential scan happening on the table instead of an
index scan.
This is query plan in the log file:
LOG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:planTree
{SEQSCAN
:startup_cost 0.00
:total_cost 2877.58
:plan_rows 429
:plan_width 103
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 16385
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 26
}
:resno 2
:resname guid
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 1043
:vartypmod 36
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 47
}
:resno 3
:resname authenticatorsending
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 1043
:vartypmod 36
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 4
:location 84
}
:resno 4
:resname authenticatorsubscription
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 126
}
:resno 5
:resname countpushed
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 5
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 6
:vartype 1114
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 6
:location 154
}
:resno 6
:resname datecreated
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 6
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 7
:vartype 1114
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 7
:location 182
}
:resno 7
:resname datelastpush
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 7
:resjunk false
}
)
:qual (
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{FUNCEXPR
:funcid 401
:funcresulttype 25
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 100
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 2
:vartype 16385
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 234
}
)
:location -1
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location -1
:constvalue 40 [ -96 0 0 0 48 48 53 51 54 49 69 56 45 51 51
69 65
45 49 70 48 69 45 66 50 49 55 45 67 57 49 66 52 65 67 55 66
67 69
54 ]
}
)
:location 254
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname push_topic
:colnames ("id" "guid" "authenticatorsending"
"authenticatorsubscript
ion" "countpushed" "datecreated" "datelastpush")
}
:rtekind 0
:relid 16393
:relkind r
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10 11 12 13 14 15)
:modifiedCols (b)
}
)
:resultRelations <>
:utilityStmt <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 16393)
:invalItems <>
:nParamExec 0
}
STATEMENT: SELECT "push_topic"."id", "push_topic"."guid",
"push_topic"."authenticatorsending", "push_topic"."authenticatorsubscription",
"push_topic"."countpushed", "push_topic"."datecreated",
"push_topic"."datelastpush" FROM "push_topic" WHERE "push_topic"."guid" = $1
LOG: duration: 115.498 ms execute query_mc_push_database_Topic: SELECT
"push_topic"."id", "push_topic"."guid", "push_topic"."authenticatorsending",
"push_topic"."authenticatorsubscription", "push_topic"."countpushed",
"push_topic"."datecreated", "push_topic"."datelastpush" FROM "push_topic" WHERE
"push_topic"."guid" = $1
Any idea how to solve this ?
Thank you
Meike
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
