Skip site navigation (1) Skip section navigation (2)

Re: Similar querys, better execution time on worst execution plan

From: "Fernando Papa" <fpapa(at)claxson(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Cc: "Programador4" <programador4(at)claxson(dot)com>, Nicolás Peralta <nperalta(at)claxson(dot)com>,"Alejandro Maierowicz" <amaierowicz(at)claxson(dot)com>
Subject: Re: Similar querys, better execution time on worst execution plan
Date: 2003-06-26 13:33:38
Message-ID: F1DC5B511E2D1C499E5E20FC6D74160D03642249@exch2000.buehuergo.corp.claxson.com (view raw or flat)
Thread:
Lists: pgsql-performance
> -----Mensaje original-----
> De: SZUCS Gábor [mailto:surrano(at)mailbox(dot)hu] 
> Enviado el: jueves, 26 de junio de 2003 7:31
> Para: pgsql-performance(at)postgresql(dot)org
> Asunto: Re: [PERFORM] Similar querys, better execution time 
> on worst execution plan
> 
> 
> Fernando,
> 
> 1. Try EXPLAIN ANALYZE. Cost alone isn't an absolute measure. 
> I think it's only to see which parts of the query are 
> expected to be slowest. However, EXP ANA will give you exact 
> times in msec (which effectively means it executes the query).

Ok, yes, I did only explay because I run several times the query and get avg. run time. but it's true, it's better to do EXP ANA.
 
> 2. I think calling upper() for each row costs more than 
> direct comparison, but not sure

It's the only answer than I can found... maybe do a lot of uppers and then compare will be too much than compare with 2 conditions...
 
> 3. Notice that there are seq scans with filter conditions like
>   "id_instalacion = 2::numeric"
>   Do you have indices on id_instalacion, which seems to be a 
> numeric field? if so, try casting the constant expressions in 
> the query to numeric so that postgresql may find the index. 
> If you don't have such indices, it may be worth to create 
> them. (I guess you only have it on the table aliased with c, 
> since it does an index scan there.

Yes, we have index on id_instalacion, but now we have only one instalation, so the content of these field, in the 99% of the rows, it's 2. I think in this case it's ok to choose seq scan.
 
> 4. another guess may be indices on (id_instalacion, activo), 
> or, if activo has few possible values (for example, it may be 
> only one of three letters, say, 'S', 'A' or 'K'), partial 
> indices like:
> 
> CREATE INDEX cont_sbc_id_ins_S ON cont_sbc (id_instalacion)
>     WHERE activo in ('S', 's');
> CREATE INDEX cont_sbc_id_ins_A ON cont_sbc (id_instalacion)
>     WHERE activo in ('A', 'a');
> CREATE INDEX cont_sbc_id_ins_K ON cont_sbc (id_instalacion)
>     WHERE activo in ('K', 'k');
> 

I need to recheck about the "quality" of "active" field. Really I don't know if I found a lot of 'S', a lot of 'N', maybe we will have 50%/50% of 'S' or 'N'. This will be important to define index.

Thanks for your answer.

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-06-26 14:08:05
Subject: Re: problem with pg_statistics
Previous:From: Andre SchubertDate: 2003-06-26 13:15:15
Subject: problem with pg_statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group