From: Ruben Rubio <ruben@rentalia.com>
To: Gregory S. Williamson <gsw@globexplorer.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimization of this SQL sentence
Date: Tue, 17 Oct 2006 11:40:08 +0200
Message-ID: <4534A4F8.7070209@rentalia.com> (raw)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A256832802B3E738@loki.wc.globexplorer.net>
References: <45349CDF.3090002@rentalia.com>
<71E37EF6B7DCC1499CEA0316A256832802B3E738@loki.wc.globexplorer.net>
-----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@postgresql.org on behalf of Ruben Rubio
> Sent: Tue 10/17/2006 2:05 AM
> To: pgsql-performance@postgresql.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@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349c86275246672479766&user=gs...;
!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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruben@rentalia.com, gsw@globexplorer.com
Subject: Re: Optimization of this SQL sentence
In-Reply-To: <4534A4F8.7070209@rentalia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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