From: _moray <moray1.geo@yahoo.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
Subject: a very big table
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 121
Message-ID: <lkf2e.2987$aH3.649@news.edisontel.com>
Date: Tue, 29 Mar 2005 18:25:55 +0200
Organization: EdisonTel SpA
To: pgsql-sql@postgresql.org


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.

f.i. I made a simple interface where a user can specify multiple filters 
on almost all the columns of the table and a resulting query could be:

===========
SELECT
	ripete.numero as ripete_numero,
	pubblicita.soggetto,pubblicita.colore,
	pubblicazioni.anno,pubblicazioni.numero,
	pubblicita.codice_pubblicita,pubblicita.annullata,
	pubblicita.codice_pagina,pubblicita.codice_materiale,
	pubblicita.note,pubblicita.prezzo,
	testate.testata AS testata,
	inserzionisti.sigla AS inserzionista,
	materiali.descrizione AS materiale,
	pagine.descrizione AS pagina
FROM
	pubblicita
	LEFT OUTER JOIN materiali ON 
(pubblicita.codice_materiale=materiali.codice_materiale)
	LEFT OUTER JOIN pagine ON (pubblicita.codice_pagina=pagine.codice_pagina)
	LEFT OUTER JOIN inserzionisti ON 
(pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista)
	LEFT OUTER JOIN pubblicazioni ON 
(pubblicita.codice_pubblicazione=pubblicazioni.codice_pubblicazione)
	LEFT OUTER JOIN testate ON 
(pubblicazioni.codice_testata=testate.codice_testata)
	LEFT OUTER JOIN pubblicazioni ripete ON 
(pubblicita.ripete_da=ripete.codice_pubblicazione)
WHERE
	pubblicazioni.anno ILIKE '2003%'
	AND  inserzionisti.sigla ILIKE 'starline%'
	ORDER BY testate.testata ASC LIMIT 15 OFFSET 0
===========

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
);
===========