pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeda very big table
4+ messages / 4 participants
[nested] [flat]
* a very big table
@ 2005-03-29 16:25 _moray <moray1.geo@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: _moray @ 2005-03-29 16:25 UTC (permalink / raw)
To: pgsql-sql
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
);
===========
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: a very big table
@ 2005-04-03 12:38 Sean Davis <sdavis2@mail.nih.gov>
parent: _moray <moray1.geo@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Sean Davis @ 2005-04-03 12:38 UTC (permalink / raw)
To: _moray <moray1.geo@yahoo.com>; 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: a very big table
@ 2005-04-03 18:21 PFC <lists@boutiquenumerique.com>
parent: _moray <moray1.geo@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: PFC @ 2005-04-03 18:21 UTC (permalink / raw)
To: _moray <moray1.geo@yahoo.com>; pgsql-sql
I'd suggest modifying your query generator to make it smarter :
FROM
pubblicita
LEFT OUTER JOIN materiali ON
(pubblicita.codice_materiale=materiali.codice_materiale)
LEFT OUTER JOIN inserzionisti ON
(pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista)
(snip)
WHERE
pubblicazioni.anno ILIKE '2003%'
AND inserzionisti.sigla ILIKE 'starline%'
(snip)
Here you don't need to LEFT JOIN, you can use a straight simple
unconstrained join because the rows generated by the LEFT JOINs which have
NULL in the right columns will be rejected by the WHERE clause anyway :
FROM
pubblicita, materiali, inserzionisti
(remainder of LEFT JOINs for table which have nothing in the WHERE)
(snip)
WHERE
pubblicazioni.anno ILIKE '2003%'
AND inserzionisti.sigla ILIKE 'starline%'
AND pubblicita.codice_materiale=materiali.codice_materiale
AND pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista
(snip)
Doing this, you leave more options for the planner to choose good plans,
and also to generate less of the joins (ie for instance starting on
publicazioni, taking only the rows with the date condition, and then
joining them to the other tables).
Now, other comments :
ILIKE cant' ever use an index. If you must use LIKE, use lower(column)
LIKE 'something%' and create a functional index on lower(column).
WHY IS THE DATE STORED AS TEXT ?? You could use a DATE field and query
"pubblicazioni.anno BETWEEN '2003-01-01' AND '2003-12-31'" or any other
date range. Always use the appropriate datatype. BETWEEN uses indexes.
On Tue, 29 Mar 2005 18:25:55 +0200, _moray <moray1.geo@yahoo.com> wrote:
>
> 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
> );
> ===========
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: a very big table
@ 2005-04-04 00:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-04-04 00:50 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: _moray <moray1.geo@yahoo.com>; pgsql-sql
PFC <lists@boutiquenumerique.com> writes:
> Here you don't need to LEFT JOIN, you can use a straight simple
> unconstrained join because the rows generated by the LEFT JOINs which have
> NULL in the right columns will be rejected by the WHERE clause anyway :
In recent versions of Postgres, the planner knows this and can make the
simplification for itself, at least if the operator or function used in
WHERE is properly marked as strict.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-04 00:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-29 16:25 a very big table _moray <moray1.geo@yahoo.com>
2005-04-03 12:38 ` Sean Davis <sdavis2@mail.nih.gov>
2005-04-03 18:21 ` PFC <lists@boutiquenumerique.com>
2005-04-04 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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