From: Sean Davis <sdavis2@mail.nih.gov>
To: _moray <moray1.geo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: a very big table
Date: Sun, 3 Apr 2005 08:38:41 -0400
Message-ID: <000801c5384a$115fbef0$5179f345@WATSON> (raw)
References: <lkf2e.2987$aH3.649@news.edisontel.com>
----- Original Message -----
From: "_moray" <moray1.geo@yahoo.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 29, 2005 12:25 PM
Subject: [SQL] a very big table
>
> hullo all,
>
> I have a problem with a table containing a lot of data.
>
> referred tables "inserzionista" and "pubblicazioni" (referenced 2 times)
> have resp. 1909 tuples and 8300 tuples, while this one 54942.
>
> now the problem is that it is slow, also a simple "select * from
> pubblicita". (it takes 5-6 seconds on my P4@1,6Ghz laptop...)
>
> I tried using some indexes, but the main problem is that I am using a php
> script to access the data that builds the query according to user input.
Generally, you need to have an index for any column that will appear in a
'where' clause or be referenced as a foreign key. The referencing columns
should be declared "unique" or "primary key" and will also then be indexed.
So, any column that is on the left or right of an '=' sign in a join or a
'where' clause should probably be indexed. There are exceptions, but that
is the general rule. Also, after you make your indices, you need to
remember to vacuum analyze.
> As you can see it is a quite heavy query...but also with simple queries:
>
> ===========
> cioe2=# explain SELECT * from pubblicita;
> QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on pubblicita (cost=0.00..2863.42 rows=54942 width=325)
> (1 row)
>
> cioe2=# explain SELECT * from pubblicita where soggetto ilike 'a%';
> QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on pubblicita (cost=0.00..3000.78 rows=54942 width=325)
> Filter: (soggetto ~~* 'a%'::text)
> (2 rows)
> ===========
>
> suggestions on how to make things smoother?
> (the table is below)
>
> thnx
>
> Ciro.
>
> ===========
> create table pubblicita (
> codice_pubblicita bigserial,
> codice_inserzionista int NOT NULL,
> codice_pagina varchar(2),
> codice_materiale varchar(2),
> codice_pubblicazione bigint NOT NULL,
>
> data_registrazione timestamp,
>
> ripete_da bigint,
> soggetto text,
> inserto text,
>
> prezzo numeric,
> ns_fattura int,
> ns_fattura_data date,
> vs_fattura int,
> vs_fattura_data date,
>
> colore bool,
> data_prenotazione date,
> data_arrivo date,
> data_consegna date,
> note_prenotazione text,
> note_consegna text,
>
> note text,
>
> annullata bool DEFAULT 'f',
>
> PRIMARY KEY (codice_pubblicita),
> FOREIGN KEY (codice_pubblicazione)
> REFERENCES pubblicazioni
> ON UPDATE CASCADE,
> FOREIGN KEY (ripete_da)
> REFERENCES pubblicazioni (codice_pubblicazione)
> ON UPDATE CASCADE,
> FOREIGN KEY (codice_inserzionista)
> REFERENCES inserzionisti
> ON UPDATE CASCADE,
> FOREIGN KEY (codice_pagina)
> REFERENCES pagine
> ON UPDATE CASCADE,
> FOREIGN KEY (codice_materiale)
> REFERENCES materiali
> ON UPDATE CASCADE
> );
> ===========
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sdavis2@mail.nih.gov, moray1.geo@yahoo.com
Subject: Re: a very big table
In-Reply-To: <000801c5384a$115fbef0$5179f345@WATSON>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox