----- Original Message ----- From: "_moray" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED],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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
