Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 7F44EBBDD52 for ; Fri, 18 Nov 2011 10:41:54 -0400 (AST) Received: from mail2.sdb.it ([85.37.147.67]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RRPdV-0006Xd-LH for pgsql-admin@postgresql.org; Fri, 18 Nov 2011 14:41:54 +0000 Received: by mail2.sdb.it (Postfix, from userid 500) id 192828033; Fri, 18 Nov 2011 15:41:37 +0100 (CET) Received: from geronimo.sdb.it (geronimo.sdb.it [10.101.1.20]) by mail2.sdb.it (Postfix) with ESMTP id 131C6802A; Fri, 18 Nov 2011 15:41:37 +0100 (CET) Received: from mailtech.sdb.it (localhost [127.0.0.1]) by geronimo.sdb.it (Postfix) with ESMTP id CD9963808BD; Fri, 18 Nov 2011 15:41:26 +0100 (CET) Received: from [10.118.101.88] (silviob.sdb.it [10.118.101.88]) by mailtech.sdb.it (Postfix) with ESMTP id B3FB560061; Fri, 18 Nov 2011 15:35:42 +0100 (CET) Message-ID: <4EC66EA0.9020900@tech.sdb.it> Date: Fri, 18 Nov 2011 15:41:36 +0100 From: Silvio Brandani User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.2.23) Gecko/20110920 Thunderbird/3.1.15 MIME-Version: 1.0 To: Szymon Guz Cc: pgsql-admin@postgresql.org Subject: Re: database not using indexes, yet References: <4EBA9AAC.9030900@tech.sdb.it> <4EC66726.6060800@tech.sdb.it> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060000050707090100050003" X-Private-Material: Please read notice X-Archive-Number: 201111/99 X-Sequence-Number: 38693 This is a multi-part message in MIME format. --------------060000050707090100050003 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit table is 959818 records, I create a copy of the table with create tabase as select ... and then indexed, the new table use the indexes ... thanks Il 18/11/2011 15.19, Szymon Guz ha scritto: > > > 2011/11/18 Silvio Brandani > > > On postgres 8.3.11 on linux centos 5 we have a table not too big > with primary key index on > > Indexes: > "aida_references_pkey" PRIMARY KEY, btree (aida_reference_id) > > the query not use index: > > aidadb=# explain analyze select aida_reference_id from > aida.aida_references where aida_reference_id = '3145'; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------- > Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4) > (actual time=0.173..1457.643 rows=1 loops=1) > Filter: (aida_reference_id = 3145) > Total runtime: 1457.696 ms > > > already executed the vacuum ,reindex. > > Please help > > -- > Silvio Brandani > > > > Hi Silvio, > how many rows do you have in the table? > Usually PostgreSQL doesn't want to use index when the table is small. > > > regards > Szymon > > > -- > *http://simononsoftware.com/* > -- Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119 --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalitą amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilitą giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitą lavorativa o contrari a norme. -- --------------060000050707090100050003 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable table is 959818 records,

=C2=A0I create a copy of the table with create tabase as select ... a= nd then=C2=A0 indexed,=C2=A0 the new table use the indexes ...

thanks

Il 18/11/2011 15.19, Szymon Guz ha scritto:


2011/11/18 Silvio Brandani <silvio.brandani@t= ech.sdb.it>
On postgres 8.3.11 on linux centos 5 we have a table =C2=A0not = too big =C2=A0with primary key index on

Indexes:
=C2=A0 =C2=A0"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)

the query not use index:

aidadb=3D# =C2=A0explain analyze select aida_reference_id from aida.aida_references where aida_reference_id =3D '3145';
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
---------------------------------------------------------------= -------------------------------------------------
=C2=A0Seq Scan on aida_references =C2=A0(cost=3D0.00..51489.15 = rows=3D1 width=3D4) (actual time=3D0.173..1457.643 rows=3D1 loops=3D1) =C2=A0 Filter: (aida_reference_id =3D 3145)
=C2=A0Total runtime: 1457.696 ms


already executed the vacuum ,reindex.

Please help

--
Silvio Brandani



Hi Silvio,
how many rows do you have in the table?=C2=A0
Usually PostgreSQL doesn't want to use index when the table is small.


regards
Szymon


--
--=20 Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119 =0D
=0D =0D =0D =0D disclaimer-sdb=0D =0D =0D
=0D =0D Utilizziamo i dati personali che la riguardano esclusivamente per nostre = finalità amministrative e contabili, anche quando li comunichiamo a= terzi. Informazioni dettagliate, anche in ordine al Suo diritto di acces= so e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privac= y.html=0D
=0D Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo = al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 6= 16 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.ht= ml=0D
=0D L'Azienda non si assume alcuna responsabilità giuridica qualora per= vengano da questo indirizzo messaggi estranei all'attività lavorati= va o contrari a norme.=0D =0D
=0D =0D =0D =0D

=0D =0D =0D
=0D --------------060000050707090100050003--