pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery performance problem
6+ messages / 5 participants
[nested] [flat]
* Query performance problem
@ 2004-08-20 16:25 Danilo Mota <dmota@nexen.com.br>
2004-08-20 16:34 ` Re: Query performance problem Tom Lane <tgl@sss.pgh.pa.us>
2004-08-20 16:48 ` Re: Query performance problem Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 6+ messages in thread
From: Danilo Mota @ 2004-08-20 16:25 UTC (permalink / raw)
To: pgsql-performance
HI All,
I have a big performance issue concerning a PostgreSQL database.
I have the following server configuration:
Pentium 4 2.4 GHz
1 GB RAM
36 GB SCSI
And the following tables:
TABLES
------------------------------------------------------------------------
--
================== r_cliente: 75816 records
============================
CREATE TABLE "public"."r_cliente" (
"pkcliente" INTEGER NOT NULL,
"cpfcnpj" VARCHAR(20) NOT NULL,
PRIMARY KEY("pkcliente")
) WITH OIDS;
CREATE UNIQUE INDEX "un_cliente_cpfcnpj" ON "public"."r_cliente"
USING btree ("cpfcnpj");
================== sav_cliente_lg: 65671 records
=======================
CREATE TABLE "public"."sav_cliente_lg" (
"codigo" INTEGER NOT NULL,
"cpfcnpj" VARCHAR(15) NOT NULL,
PRIMARY KEY("codigo")
) WITH OIDS;
CREATE INDEX "ix_savclientelg_cpfcnpj" ON "public"."sav_cliente_lg"
USING btree ("cpfcnpj");
Which I would like to run the following query:
QUERY
------------------------------------------------------------------------
--
SELECT
rc.pkcliente
FROM r_cliente AS rc
INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj;
The problem is, it takes a long time to run, I wait up to half an hour
and I get no result.
So, I executed the explain on the query and got the following results:
QUERY PLAN
------------------------------------------------------------------------
--
Nested Loop (cost=0.00..16696.87 rows=75816 width=4)
-> Seq Scan on sav_cliente_cf sc (cost=0.00..3047.55 rows=1
width=0)
Filter: ((cpfcnpj)::text = (cpfcnpj)::text)
-> Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816
width=4)
And made the following modifications on my POSTGRESQL.CONF file:
POSTGRESQL.CONF
------------------------------------------------------------------------
--
### VERSION: Postgresql 7.4.2 ###
shared_buffers = 7800
sort_mem = 4096
checkpoint_segments = 5
effective_cache_size = 12000
cpu_operator_cost = 0.0015
stats_start_collector = false
Hope you can help me, I really need to get this running faster, and I am
out of ideas.
Since now, thanks a lot for your attention,
Danilo Mota
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance problem
2004-08-20 16:25 Query performance problem Danilo Mota <dmota@nexen.com.br>
@ 2004-08-20 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-08-20 16:34 UTC (permalink / raw)
To: dmota@nexen.com.br; +Cc: pgsql-performance
"Danilo Mota" <dmota@nexen.com.br> writes:
> SELECT
> rc.pkcliente
> FROM r_cliente AS rc
> INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj;
Surely you meant
INNER JOIN sav_cliente_lg AS sc ON rc.cpfcnpj = sc.cpfcnpj;
I would also venture that your statistics are desperately out of date,
because if the planner's estimates are close to reality, even this
unconstrained-cross-product join shouldn't have taken that long.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance problem
2004-08-20 16:25 Query performance problem Danilo Mota <dmota@nexen.com.br>
@ 2004-08-20 16:48 ` Bruno Wolff III <bruno@wolff.to>
2004-08-20 16:50 ` Re: Query performance problem Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-08-20 16:48 UTC (permalink / raw)
To: Danilo Mota <dmota@nexen.com.br>; +Cc: pgsql-performance
On Fri, Aug 20, 2004 at 13:25:30 -0300,
Danilo Mota <dmota@nexen.com.br> wrote:
>
> And the following tables:
> TABLES
> ------------------------------------------------------------------------
> --
> ================== r_cliente: 75816 records
> ============================
> CREATE TABLE "public"."r_cliente" (
> "pkcliente" INTEGER NOT NULL,
> "cpfcnpj" VARCHAR(20) NOT NULL,
> PRIMARY KEY("pkcliente")
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX "un_cliente_cpfcnpj" ON "public"."r_cliente"
> USING btree ("cpfcnpj");
>
> ================== sav_cliente_lg: 65671 records
> =======================
> CREATE TABLE "public"."sav_cliente_lg" (
> "codigo" INTEGER NOT NULL,
> "cpfcnpj" VARCHAR(15) NOT NULL,
> PRIMARY KEY("codigo")
> ) WITH OIDS;
>
> CREATE INDEX "ix_savclientelg_cpfcnpj" ON "public"."sav_cliente_lg"
> USING btree ("cpfcnpj");
>
>
>
> Which I would like to run the following query:
>
> QUERY
> ------------------------------------------------------------------------
> --
> SELECT
> rc.pkcliente
> FROM r_cliente AS rc
> INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj;
I am going to assume that one of the sc.cpfcnpj's above is really rc.cpfcnpj
since that corresponds to the explain below.
sc.cpfcnpj and rc.cpfcnpj are different length varchars. You made need
an explicit cast to allow the use of indexes. (Unless there is a real
business rule that mandates the limits you have used, you probably want
to make them both type 'text'.)
Another potential problem is not having analyzed the tables. I don't think
this can be ruled out based on what you have showed us so far.
>
> So, I executed the explain on the query and got the following results:
Generally you want to run EXPLAIN ANALYZE results when submitting questions
about performance problems rather than just EXPLAIN results.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance problem
2004-08-20 16:25 Query performance problem Danilo Mota <dmota@nexen.com.br>
2004-08-20 16:48 ` Re: Query performance problem Bruno Wolff III <bruno@wolff.to>
@ 2004-08-20 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-08-20 16:50 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Danilo Mota <dmota@nexen.com.br>; pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> I am going to assume that one of the sc.cpfcnpj's above is really rc.cpfcnpj
> since that corresponds to the explain below.
No, actually the explain plan corresponds to the sc.cpfcnpj = sc.cpfcnpj
condition. I didn't twig to the typo until I started to wonder why the
plan had the condition in the wrong place (attached to the seqscan and
not the join step).
> sc.cpfcnpj and rc.cpfcnpj are different length varchars. You made need
> an explicit cast to allow the use of indexes.
AFAIK the cross-type issues only apply to crossing actual types, not
lengths. That does look like an error in the database schema, though.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Query Performance Problem
@ 2014-10-21 12:57 john@jpm-cola.com
2014-10-21 13:16 ` Re: Query Performance Problem Felipe Santos <felipepts@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: john@jpm-cola.com @ 2014-10-21 12:57 UTC (permalink / raw)
To: pgsql-performance
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><br></div><div><br></div><div><span>Hi all,</span></div><div><br></div><div>I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:</div><div><br></div><div><div><font face="courier new, monospace">explain (analyze, buffers)</font></div><div><font face="courier new, monospace">select <a href="http://response.id/"; target="_blank">response.id</a></font></div><div><font face="courier new, monospace">from claim.response</font></div><div><font face="courier new, monospace">where response.account_id = 4766<br></font></div><div><font face="courier new, monospace">and response.expire_timestamp is null</font></div><div><font face="courier new, monospace">and response.create_timestamp >= DATE '2014-08-01'</font></div><div><font face="courier new, monospace">order by create_timestamp;</font></div></div><div><br></div><div>The response table looks like this:</div><div><div><font face="courier new, monospace">"account_id";"integer"</font></div><div><font face="courier new, monospace">"file_type_id";"integer"</font></div><div><font face="courier new, monospace">"receiver_inbound_detail_id";"<wbr>integer"</font></div><div><font face="courier new, monospace">"processing_status_id";"<wbr>integer"</font></div><div><font face="courier new, monospace">"processing";"boolean"</font></div><div><font face="courier new, monospace">"expire_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"last_mod_timestamp";"<wbr>timestamp without time zone"</font></div><div><font face="courier new, monospace">"create_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"response_trace_nbr";"<wbr>character varying"</font></div><div><font face="courier new, monospace">"posted_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"need_to_post";"boolean"</font></div><div><font face="courier new, monospace">"response_message";"text"</font></div><div><font face="courier new, monospace">"worked";"boolean"</font></div><div><font face="courier new, monospace">"response_status_id";"integer"</font></div><div><font face="courier new, monospace">"response_type_id";"integer"</font></div><div><font face="courier new, monospace">"outbound_claim_detail_id";"<wbr>bigint"</font></div><div><font face="courier new, monospace">"id";"bigint"</font></div></div><div><br></div><div>Here are some rowcounts:</div><div><br></div><div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201408;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">---------</font></div><div><font face="courier new, monospace"> 4585746</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 7271.054 ms</font></div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201409;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">---------</font></div><div><font face="courier new, monospace"> 3523370</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 4341.116 ms</font></div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201410;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">-------</font></div><div><font face="courier new, monospace"> 154</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 0.258 ms</font></div></div><div><br></div><div>The entire table has 225,665,512 rows. I read that a partitioning rule of thumb is that benefits of partitioning occur starting around 100 million rows.</div><div><br></div><div><div><font face="courier new, monospace">SELECT count(*) from claim.response;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">-----------</font></div><div><font face="courier new, monospace"> 225665512</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 685064.637 ms</font></div></div><div><br></div><div><br></div><div>The partitioning is on the create_timestamp field.</div><div><br></div><div>The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:</div><div><br></div><div><div><font face="courier new, monospace">Architecture: x86_64</font></div><div><font face="courier new, monospace">CPU op-mode(s): 32-bit, 64-bit</font></div><div><font face="courier new, monospace">Byte Order: Little Endian</font></div><div><font face="courier new, monospace">CPU(s): 2</font></div><div><font face="courier new, monospace">On-line CPU(s) list: 0,1</font></div><div><font face="courier new, monospace">Thread(s) per core: 1</font></div><div><font face="courier new, monospace">Core(s) per socket: 2</font></div><div><font face="courier new, monospace">CPU socket(s): 1</font></div><div><font face="courier new, monospace">NUMA node(s): 1</font></div><div><font face="courier new, monospace">Vendor ID: GenuineIntel</font></div><div><font face="courier new, monospace">CPU family: 6</font></div><div><font face="courier new, monospace">Model: 44</font></div><div><font face="courier new, monospace">Stepping: 2</font></div><div><font face="courier new, monospace">CPU MHz: 2660.000</font></div><div><font face="courier new, monospace">BogoMIPS: 5320.00</font></div><div><font face="courier new, monospace">L1d cache: 32K</font></div><div><font face="courier new, monospace">L1i cache: 32K</font></div><div><font face="courier new, monospace">L2 cache: 256K</font></div><div><font face="courier new, monospace">L3 cache: 12288K</font></div><div><font face="courier new, monospace">NUMA node0 CPU(s): 0,1</font></div></div><div><br></div><div><br></div><div><br></div><div>2 users, load average: 0.00, 0.12, 0.37</div><div><br></div><div><br></div><div>Please see the following for the explain analysis :</div><div><br></div><div><a href="http://explain.depesz.com/s/I3SL"; target="_blank">http://explain.depesz.com/s/<wbr>I3SL</a><br></div><div><br><... trying to understand why I'm getting the yellow, orange, and red on the inclusive, and the yellow on the exclusive. (referring to the <a href="http://explain.depesz.com/s/I3SL"; target="_blank">explain.depesz.com/s/I3SL</a> page.)</div><div>I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please point out anything else you can decipher from this. </div><div><br></div><div>Thanks,</div><div><br></div><div style="">John</div></span></body></html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query Performance Problem
2014-10-21 12:57 Query Performance Problem john@jpm-cola.com
@ 2014-10-21 13:16 ` Felipe Santos <felipepts@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Felipe Santos @ 2014-10-21 13:16 UTC (permalink / raw)
To: john@jpm-cola.com; +Cc: pgsql-performance
2014-10-21 10:57 GMT-02:00 <john@jpm-cola.com>:
>
>
> Hi all,
>
> I'm experimenting with table partitioning though inheritance. I'm testing
> a query as follows:
>
> explain (analyze, buffers)
> select response.id
> from claim.response
> where response.account_id = 4766
> and response.expire_timestamp is null
> and response.create_timestamp >= DATE '2014-08-01'
> order by create_timestamp;
>
> The response table looks like this:
> "account_id";"integer"
> "file_type_id";"integer"
> "receiver_inbound_detail_id";"integer"
> "processing_status_id";"integer"
> "processing";"boolean"
> "expire_timestamp";"timestamp without time zone"
> "last_mod_timestamp";"timestamp without time zone"
> "create_timestamp";"timestamp without time zone"
> "response_trace_nbr";"character varying"
> "posted_timestamp";"timestamp without time zone"
> "need_to_post";"boolean"
> "response_message";"text"
> "worked";"boolean"
> "response_status_id";"integer"
> "response_type_id";"integer"
> "outbound_claim_detail_id";"bigint"
> "id";"bigint"
>
> Here are some rowcounts:
>
> SELECT count(*) from claim_response.response_201408;
> count
> ---------
> 4585746
> (1 row)
>
> Time: 7271.054 ms
> SELECT count(*) from claim_response.response_201409;
> count
> ---------
> 3523370
> (1 row)
>
> Time: 4341.116 ms
> SELECT count(*) from claim_response.response_201410;
> count
> -------
> 154
> (1 row)
>
> Time: 0.258 ms
>
> The entire table has 225,665,512 rows. I read that a partitioning rule of
> thumb is that benefits of partitioning occur starting around 100 million
> rows.
>
> SELECT count(*) from claim.response;
> count
> -----------
> 225665512
> (1 row)
>
> Time: 685064.637 ms
>
>
> The partitioning is on the create_timestamp field.
>
> The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a
> VM machine - 8 GB RAM with 2 CPUs:
>
> Architecture: x86_64
> CPU op-mode(s): 32-bit, 64-bit
> Byte Order: Little Endian
> CPU(s): 2
> On-line CPU(s) list: 0,1
> Thread(s) per core: 1
> Core(s) per socket: 2
> CPU socket(s): 1
> NUMA node(s): 1
> Vendor ID: GenuineIntel
> CPU family: 6
> Model: 44
> Stepping: 2
> CPU MHz: 2660.000
> BogoMIPS: 5320.00
> L1d cache: 32K
> L1i cache: 32K
> L2 cache: 256K
> L3 cache: 12288K
> NUMA node0 CPU(s): 0,1
>
>
>
> 2 users, load average: 0.00, 0.12, 0.37
>
>
> Please see the following for the explain analysis :
>
> http://explain.depesz.com/s/I3SL
>
> I'm trying to understand why I'm getting the yellow, orange, and red on
> the inclusive, and the yellow on the exclusive. (referring to the
> explain.depesz.com/s/I3SL page.)
> I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some
> time. I suspect the I/O may be dragging but I don't know how to dig that
> information out from here. Please point out anything else you can decipher
> from this.
>
> Thanks,
>
> John
>
Hi John,
Dont know about the colors, but the Stats tab looks fine. You've got
yourself 5 Index Scans, which are a very fast way to dig data.
I noticed you've also cast your filter field "(create_timestamp >=
'2014-08-01'::date)". As far as I know, Postgresql doesn't need this kind
of explicit conversion. You would be fine with just "(create_timestamp >=
'2014-08-01')".
Regards,
Felipe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-10-21 13:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-20 16:25 Query performance problem Danilo Mota <dmota@nexen.com.br>
2004-08-20 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-20 16:48 ` Bruno Wolff III <bruno@wolff.to>
2004-08-20 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-21 12:57 Query Performance Problem john@jpm-cola.com
2014-10-21 13:16 ` Re: Query Performance Problem Felipe Santos <felipepts@gmail.com>
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