Re: a very big table - Mailing list 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_materialeAND
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
fieldand 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
>