Re: a very big table - Mailing list pgsql-sql

----- 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
>