Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDSEF-0006Ud-Jj for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 08:00:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDSEE-000351-U1 for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 08:00:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDSCY-000195-9K for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 07:58:58 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84_2) (envelope-from ) id 1bDSCP-00014j-Ui for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 07:58:57 +0000 Received: from [217.6.217.234] by 3capp-gmx-bs63.server.lan (via HTTP); Thu, 16 Jun 2016 09:58:46 +0200 MIME-Version: 1.0 Message-ID: From: meike.talbach@women-at-work.org To: pgsql-performance@postgresql.org Subject: Index not used Content-Type: text/plain; charset=UTF-8 Date: Thu, 16 Jun 2016 09:58:46 +0200 Importance: normal Sensitivity: Normal Content-Transfer-Encoding: quoted-printable X-Priority: 3 X-Provags-ID: V03:K0:jLSCw9vILZ0BKtpxVjVl5aImCJCBQvDj3kzWk5L7Zjl r/BRlEzM7Gbdymre5RN7n/lkyXSCb6DO9cOZ2/QFbQgX+c7w/j WA8axDlQo/YtYHXFs/5JfWIiZPZZ9irK4E1HjifAX95yuUDSue annJ6i+dwS7eQyJlv2kbmpVPAMMcgOoHzMSX0+kx7oIobJzGbw qXMLd5IXwW2MqbBHg0UAqzWez3nfJqNO38aCA0ovl99yo8JmTC wQe+XjEg9dbBOW/Bf/0ETlnlFSbaaw/2idSnc6DSxEJcdzdf8O eF7wSY= X-UI-Out-Filterresults: notjunk:1;V01:K0:LzJHLA0oBXw=:yNRkoPzQHw9Nizda+h0usY Sg0hV4LporYAZkuItdhaCj/C8bQbisXnaSlvkiiPj9t9HRxLopiiCvuJyMqzE6VZPF97T0kHK QOlN4rISIjB9v5NMchHn6MGMVcaMmLGfTwDmGExHkGbLckXF3I0MwDul5h8mQk7j7NiJ8DdS5 kOZ2j4ZCcGZpqc0qUJgCCarQk7QpJppucDg06t58dpP8LX2xzmGDphp8oIERJt54mdq7925vj Z7Su5KkqhgQpapLMDPcNUMIPG0QZn5d9Kb99qJTnS4GUpVIx2FUyIipGp+lfCKuFgh9X/kwM4 VGGuKxy2jlG1I43sx9GcQSjXUta2eFIFkfJeGsjD5VnYUG6mtAp76tjIU+YDdOWm598MW+cdZ Y3EIGTkghyplkEOcdjFM509Z9SGl/is++83MgfzPFtp4CuRkDNy6aGTNihaNKyvU5ujPQiN2x ydXGs2XMpA== X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Hello, =C2=A0 I've a basic table with about 100K rows: =C2=A0 CREATE TABLE "public"."push_topic" ( =C2=A0"id" Serial PRIMARY KEY, =C2=A0"guid" public.push_guid NOT NULL, =C2=A0"authenticatorsending" Varchar(32) NOT NULL, =C2=A0"authenticatorsubscription" Varchar(32) NOT NULL, =C2=A0"countpushed" Integer NOT NULL, =C2=A0"datecreated" timestamp NOT NULL, =C2=A0"datelastpush" timestamp ) CREATE UNIQUE INDEX push_topic_idx_topicguid ON push_topic =C2=A0 USING btree (guid) =C2=A0 When I query this through pgsql, the queries are fast as expected. This is the query: select * from push_topic where guid =3D 'DD748CCD-B8A4-3B9F-8F60-67F1F673CF= E5' And the plan: Index Scan using push_topic_idx_topicguid on push_topic (cost=3D0.42..8.44= rows=3D1 width=3D103) (actual time=3D0.117..0.121 rows=3D1 loops=3D1) Index Cond: ((guid)::bpchar =3D 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::b= pchar) Buffers: shared hit=3D3 read=3D1 Total runtime: 0.191 ms However when I run the exact query through a different application (CodeSyn= thesis 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=20 :commandType 1=20 :queryId 0=20 :hasReturning false=20 :hasModifyingCTE false=20 :canSetTag true=20 :transientPlan false=20 :planTree=20 {SEQSCAN=20 :startup_cost 0.00=20 :total_cost 2877.58=20 :plan_rows 429=20 :plan_width 103=20 :targetlist ( {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 1=20 :vartype 23=20 :vartypmod -1=20 :varcollid 0=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 1=20 :location 7 } :resno 1=20 :resname id=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 1=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 2=20 :vartype 16385=20 :vartypmod -1=20 :varcollid 100=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 2=20 :location 26 } :resno 2=20 :resname guid=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 2=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 3=20 :vartype 1043=20 :vartypmod 36=20 :varcollid 100=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 3=20 :location 47 } :resno 3=20 :resname authenticatorsending=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 3=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 4=20 :vartype 1043=20 :vartypmod 36=20 :varcollid 100=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 4=20 :location 84 } :resno 4=20 :resname authenticatorsubscription=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 4=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 5=20 :vartype 23=20 :vartypmod -1=20 :varcollid 0=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 5=20 :location 126 } :resno 5=20 :resname countpushed=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 5=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 6=20 :vartype 1114=20 :vartypmod -1=20 :varcollid 0=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 6=20 :location 154 } :resno 6=20 :resname datecreated=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 6=20 :resjunk false } {TARGETENTRY=20 :expr=20 {VAR=20 :varno 1=20 :varattno 7=20 :vartype 1114=20 :vartypmod -1=20 :varcollid 0=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 7=20 :location 182 } :resno 7=20 :resname datelastpush=20 :ressortgroupref 0=20 :resorigtbl 16393=20 :resorigcol 7=20 :resjunk false } ) :qual ( {OPEXPR=20 :opno 98=20 :opfuncid 67=20 :opresulttype 16=20 :opretset false=20 :opcollid 0=20 :inputcollid 100=20 :args ( {FUNCEXPR=20 :funcid 401=20 :funcresulttype 25=20 :funcretset false=20 :funcvariadic false=20 :funcformat 2=20 :funccollid 100=20 :inputcollid 100=20 :args ( {VAR=20 :varno 1=20 :varattno 2=20 :vartype 16385=20 :vartypmod -1=20 :varcollid 100=20 :varlevelsup 0=20 :varnoold 1=20 :varoattno 2=20 :location 234 } ) :location -1 } {CONST=20 :consttype 25=20 :consttypmod -1=20 :constcollid 100=20 :constlen -1=20 :constbyval false=20 :constisnull false=20 :location -1=20 :constvalue 40 [ -96 0 0 0 48 48 53 51 54 49 69 56 45 51 51 69= 65=20 45 49 70 48 69 45 66 50 49 55 45 67 57 49 66 52 65 67 55 66 67= 69=20 54 ] } ) :location 254 } ) :lefttree <>=20 :righttree <>=20 :initPlan <>=20 :extParam (b) :allParam (b) :scanrelid 1 } :rtable ( {RTE=20 :alias <>=20 :eref=20 {ALIAS=20 :aliasname push_topic=20 :colnames ("id" "guid" "authenticatorsending" "authenticatorsubsc= ript ion" "countpushed" "datecreated" "datelastpush") } :rtekind 0=20 :relid 16393=20 :relkind r=20 :lateral false=20 :inh false=20 :inFromCl true=20 :requiredPerms 2=20 :checkAsUser 0=20 :selectedCols (b 9 10 11 12 13 14 15) :modifiedCols (b) } ) :resultRelations <>=20 :utilityStmt <>=20 :subplans <>=20 :rewindPlanIDs (b) :rowMarks <>=20 :relationOids (o 16393) :invalItems <>=20 :nParamExec 0 } =09 STATEMENT: SELECT "push_topic"."id", "push_topic"."guid", "push_topic"."au= thenticatorsending", "push_topic"."authenticatorsubscription", "push_topic"= ."countpushed", "push_topic"."datecreated", "push_topic"."datelastpush" FRO= M "push_topic" WHERE "push_topic"."guid" =3D $1 LOG: duration: 115.498 ms execute query_mc_push_database_Topic: SELECT "p= ush_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" =3D $1 Any idea how to solve this ? Thank you Meike --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance