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

Re: Optimization of this SQL sentence

From: Ruben Rubio <ruben(at)rentalia(dot)com>
To: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimization of this SQL sentence
Date: 2006-10-17 09:40:08
Message-ID: 4534A4F8.7070209@rentalia.com (view raw or flat)
Thread:
Lists: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



If just just realized that is a litlle faster (15% faster) with this:

CREATE INDEX idx_statustype
   ON "comment" USING btree (idstatus, ctype);

Any other ideas?


Gregory S. Williamson escribió:
> Off hanbd I can't recommend anything, bur perhaps you could post the details of the tables (columns, indexes),and some info on what version of postgres you are using.
> 
> Are the tables recently analyzed ? How many rows in them ?
> 
> Greg Williamson
> DBA
> GlobeXplorer LLC
> 
> 
> -----Original Message-----
> From:	pgsql-performance-owner(at)postgresql(dot)org on behalf of Ruben Rubio
> Sent:	Tue 10/17/2006 2:05 AM
> To:	pgsql-performance(at)postgresql(dot)org
> Cc:	
> Subject:	[PERFORM] Optimization of this SQL sentence
> 
> This SQL sentence is very simple. I need to get better results. I have
> tried some posibilities and I didn't get good results.
> 
> SELECT max(idcomment)
>  FROM ficha vf
>  INNER JOIN comment c ON (vf.idficha=c.idfile AND (idestado=3 OR
> idestado=4))
>  WHERE idstatus=3
>  AND ctype=1
> 
> 
> QUERY PLAN
> 
> Aggregate  (cost=2730.75..2730.76 rows=1 width=4) (actual
> time=188.463..188.469 rows=1 loops=1)
> 
>   ->  Hash Join  (cost=1403.44..2730.72 rows=11 width=4) (actual
> time=141.464..185.404 rows=513 loops=1)
> 
>         Hash Cond: ("outer".idfile = "inner".idficha)
> 
>         ->  Seq Scan on "comment" c  (cost=0.00..1321.75 rows=1083
> width=8) (actual time=0.291..36.112 rows=642 loops=1)
> 
>               Filter: ((idstatus = 3) AND (ctype = 1))
> 
>         ->  Hash  (cost=1403.00..1403.00 rows=178 width=4) (actual
> time=141.004..141.004 rows=6282 loops=1)
> 
>               ->  Seq Scan on ficha vf  (cost=0.00..1403.00 rows=178
> width=4) (actual time=0.071..97.885 rows=6282 loops=1)
> 
>                     Filter: (((idestado)::text = '3'::text) OR
> ((idestado)::text = '4'::text))
> 
> Total runtime: 188.809 ms
> 
> 
> Thanks in advance,
> Ruben Rubio

- ---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq


- -------------------------------------------------------
Click link below if it is SPAM gsw(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349c86275246672479766&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:45349c86275246672479766!
- -------------------------------------------------------






- ---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFNKT4Io1XmbAXRboRAurtAKC8YWjgzytaqkPjLfrohZ1aceZivwCgpDii
wzxc4fktzIHTZRhPuJLi2Wc=
=Korn
-----END PGP SIGNATURE-----

In response to

pgsql-performance by date

Next:From: A. KretschmerDate: 2006-10-17 09:48:41
Subject: Re: Optimization of this SQL sentence
Previous:From: Ruben RubioDate: 2006-10-17 09:33:18
Subject: Re: Optimization of this SQL sentence

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