pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimizing query
11+ messages / 7 participants
[nested] [flat]
* optimizing query
@ 2003-01-22 10:30 Chantal Ackermann <chantal.ackermann@biomax.de>
0 siblings, 1 reply; 11+ messages in thread
From: Chantal Ackermann @ 2003-01-22 10:30 UTC (permalink / raw)
To: pgsql-general@postgresql.org
hello all,
I am getting the following output from EXPLAIN, concerning a query with
joins. The merge uses index scans but takes too long, in my opinion. The
query is in fact only a part (subquery) of another one, but it is the
bottle neck.
As I am quite ignorant in optimizing queries, and I have no idea where
to find documentation on the net on how to learn optimizing my queries,
I am posting this here in hope someone will give me either tips how to
optimize, or where to find some tutorial that could help me get along on
my own.
dropping the "DISTINCT" has some effect, but I can't really do without.
Thank you
Chantal
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
relate=# explain SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM disease, gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
disease.disease_id=disease_occurrences_puid.disease_id AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=426503.59..436839.47 rows=137812 width=41)
-> Sort (cost=426503.59..429948.88 rows=1378118 width=41)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Hash Join (cost=67813.96..162375.07 rows=1378118 width=41)
Hash Cond: ("outer".disease_id = "inner".disease_id)
-> Merge Join (cost=63671.50..98237.36 rows=1378118
width=37)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14538.05 rows=471915 width=8)
-> Sort (cost=63671.50..64519.87 rows=339347
width=29)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22828.18
rows=339347 width=29)
Merge Cond: ("outer".gene_id =
"inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7668.59 rows=218085 width=21)
-> Index Scan using gene_id_puid_uni
on gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8)
-> Hash (cost=3167.97..3167.97 rows=164597 width=4)
-> Seq Scan on disease (cost=0.00..3167.97
rows=164597 width=4)
(16 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: optimizing query
@ 2003-01-22 16:20 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Chantal Ackermann <chantal.ackermann@biomax.de>
0 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2003-01-22 16:20 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: pgsql-general@postgresql.org; pgsql-performance
(Replying to general and performance in a hope to move this
to performance after a couple of replies).
On Wed, 22 Jan 2003, Chantal Ackermann wrote:
> I am getting the following output from EXPLAIN, concerning a query with
> joins. The merge uses index scans but takes too long, in my opinion. The
> query is in fact only a part (subquery) of another one, but it is the
> bottle neck.
>
> As I am quite ignorant in optimizing queries, and I have no idea where
> to find documentation on the net on how to learn optimizing my queries,
> I am posting this here in hope someone will give me either tips how to
> optimize, or where to find some tutorial that could help me get along on
> my own.
>
> dropping the "DISTINCT" has some effect, but I can't really do without.
The first thing is, have you done ANALYZE recently to make sure that the
statistics are correct and what does EXPLAIN ANALYZE give you (that will
run the query and give the estimate and actual). Also, if you haven't
vacuumed recently, you may want to vacuum full.
How many rows are there on gene, disease and both occurrances tables?
I'd wonder if perhaps using explicit sql join syntax (which postgres uses
to constrain order) to join disease and disease_occurrences_puid before
joining it to the other two would be better or worse in practice.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: optimizing query
@ 2003-01-23 09:16 Chantal Ackermann <chantal.ackermann@biomax.de>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 11+ messages in thread
From: Chantal Ackermann @ 2003-01-23 09:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-general@postgresql.org; pgsql-performance
hi Stephan,
thank you for your reply.
I ran vacuum analyze before calling explain. As this is a newly built
database where no rows have been deleted, yet, I thought vacuum full
would have no effect. In fact, BEFORE running vacuum full, the cost of
the query is estimates by explain analyze as 33 secs, and AFTER running
it, the cost is estimate to be 43 secs??? (Hey, I want at least the 10
secs back ;-) )
I have just installed this database on a "bigger" (see the system info
further down) machine, and I expected the queries would run _really_
fast. especially, as there is a lot more data to be inserted in the
occurrences tables.
This is the row count of the tables and the output of explain analyze
before and after running vacuum full (after that, I listed some system
and postgresql information):
relate=# select count(*) from gene;
count
--------
218085
(1 row)
relate=# select count(*) from disease;
count
--------
164597
(1 row)
relate=# select count(*) from disease_occurrences_puid;
count
--------
471915
(1 row)
relate=# select count(*) from gene_occurrences_puid;
count
--------
339347
(1 row)
relate=# explain analyze SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=342175.89..352511.77 rows=137812 width=33) (actual
time=32112.66..33139.23 rows=219435 loops=1)
-> Sort (cost=342175.89..345621.18 rows=1378118 width=33) (actual
time=32112.65..32616.14 rows=695158 loops=1)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Merge Join (cost=63671.50..98237.36 rows=1378118
width=33) (actual time=10061.83..17940.02 rows=695158 loops=1)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14538.05 rows=471915 width=4)
(actual time=0.03..3917.99 rows=471915 loops=1)
-> Sort (cost=63671.50..64519.87 rows=339347 width=29)
(actual time=10061.69..10973.64 rows=815068 loops=1)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22828.18 rows=339347
width=29) (actual time=0.21..3760.59 rows=339347 loops=1)
Merge Cond: ("outer".gene_id = "inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7668.59 rows=218085 width=21) (actual time=0.02..955.19
rows=218073 loops=1)
-> Index Scan using gene_id_puid_uni on
gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
time=0.02..1523.81 rows=339347 loops=1)
Total runtime: 33244.81 msec
(13 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AFTER
relate=# vacuum full verbose analyze;
relate=# explain analyze SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=359069.64..369948.41 rows=145050 width=33) (actual
time=42195.60..43229.04 rows=219435 loops=1)
-> Sort (cost=359069.64..362695.90 rows=1450503 width=33) (actual
time=42195.59..42694.70 rows=695158 loops=1)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Merge Join (cost=63732.51..99264.24 rows=1450503
width=33) (actual time=13172.40..27973.79 rows=695158 loops=1)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14543.06 rows=471915 width=4)
(actual time=36.50..10916.29 rows=471915 loops=1)
-> Sort (cost=63732.51..64580.88 rows=339347 width=29)
(actual time=13126.56..14048.38 rows=815068 loops=1)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22889.19 rows=339347
width=29) (actual time=58.00..6775.55 rows=339347 loops=1)
Merge Cond: ("outer".gene_id = "inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7739.91 rows=218085 width=21) (actual time=29.00..3416.01
rows=218073
loops=1)
-> Index Scan using gene_id_puid_uni on
gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
time=28.69..1936.83 rows=339347 loops=1)
Total runtime: 43338.94 msec
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Postgres Version: 7.3.1
CPU: 1666.767 MHz
RAM: 2070492 kB
shmmax/shmall: 1048576000
postgresql.conf:
shared_buffers: 121600
max_connections: 64
max_fsm_relations = 200
max_fsm_pages = 40000
effective_cache_size = 8000
********************************************************************
Thank you again for your interest and help!
Chantal
Stephan Szabo wrote:
> (Replying to general and performance in a hope to move this
> to performance after a couple of replies).
>
> On Wed, 22 Jan 2003, Chantal Ackermann wrote:
>
>
>>I am getting the following output from EXPLAIN, concerning a query with
>>joins. The merge uses index scans but takes too long, in my opinion. The
>>query is in fact only a part (subquery) of another one, but it is the
>>bottle neck.
>>
>>As I am quite ignorant in optimizing queries, and I have no idea where
>>to find documentation on the net on how to learn optimizing my queries,
>>I am posting this here in hope someone will give me either tips how to
>>optimize, or where to find some tutorial that could help me get along on
>>my own.
>>
>>dropping the "DISTINCT" has some effect, but I can't really do without.
>
>
> The first thing is, have you done ANALYZE recently to make sure that the
> statistics are correct and what does EXPLAIN ANALYZE give you (that will
> run the query and give the estimate and actual). Also, if you haven't
> vacuumed recently, you may want to vacuum full.
>
> How many rows are there on gene, disease and both occurrances tables?
> I'd wonder if perhaps using explicit sql join syntax (which postgres uses
> to constrain order) to join disease and disease_occurrences_puid before
> joining it to the other two would be better or worse in practice.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: optimizing query
@ 2003-01-23 15:05 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Chantal Ackermann <chantal.ackermann@biomax.de>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2003-01-23 15:05 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: pgsql-general@postgresql.org; pgsql-performance
On Thu, 23 Jan 2003, Chantal Ackermann wrote:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Postgres Version: 7.3.1
> CPU: 1666.767 MHz
> RAM: 2070492 kB
> shmmax/shmall: 1048576000
>
> postgresql.conf:
> shared_buffers: 121600
> max_connections: 64
> max_fsm_relations = 200
> max_fsm_pages = 40000
> effective_cache_size = 8000
>
> ********************************************************************
Hmm, how about how many pages are in the various tables, (do a
vacuum verbose <table> for the various tables and what is sort_mem
set to? It's picking the index scan to get the tables in sorted
order, but I wonder if that's really the best plan given it's getting
a large portion of the tables.
Hmm, what does it do if you set enable_indexscan=off; ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] optimizing query
@ 2003-01-23 15:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chantal Ackermann <chantal.ackermann@biomax.de>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2003-01-23 15:26 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-general@postgresql.org; pgsql-performance
Chantal Ackermann <chantal.ackermann@biomax.de> writes:
> Unique (cost=359069.64..369948.41 rows=145050 width=33) (actual
> time=42195.60..43229.04 rows=219435 loops=1)
> -> Sort (cost=359069.64..362695.90 rows=1450503 width=33) (actual
> time=42195.59..42694.70 rows=695158 loops=1)
> Sort Key: gene.gene_name, gene_occurrences_puid.puid
> -> Merge Join (cost=63732.51..99264.24 rows=1450503
> width=33) (actual time=13172.40..27973.79 rows=695158 loops=1)
> Merge Cond: ("outer".puid = "inner".puid)
> -> Index Scan using disease_occpd_puid_i on
> disease_occurrences_puid (cost=0.00..14543.06 rows=471915 width=4)
> (actual time=36.50..10916.29 rows=471915 loops=1)
> -> Sort (cost=63732.51..64580.88 rows=339347 width=29)
> (actual time=13126.56..14048.38 rows=815068 loops=1)
> Sort Key: gene_occurrences_puid.puid
> -> Merge Join (cost=0.00..22889.19 rows=339347
> width=29) (actual time=58.00..6775.55 rows=339347 loops=1)
> Merge Cond: ("outer".gene_id = "inner".gene_id)
> -> Index Scan using gene_pkey on gene
> (cost=0.00..7739.91 rows=218085 width=21) (actual time=29.00..3416.01
> rows=218073
> loops=1)
> -> Index Scan using gene_id_puid_uni on
> gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
> time=28.69..1936.83 rows=339347 loops=1)
> Total runtime: 43338.94 msec
Seems like most of the time is going into the sort steps.
> postgresql.conf:
> shared_buffers: 121600
> max_connections: 64
> max_fsm_relations = 200
> max_fsm_pages = 40000
> effective_cache_size = 8000
Try increasing sort_mem.
Also, I'd back off on shared_buffers if I were you. There's no evidence
that values above a few thousand buy anything.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] optimizing query
@ 2003-01-23 15:52 Chantal Ackermann <chantal.ackermann@biomax.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Chantal Ackermann @ 2003-01-23 15:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-general@postgresql.org; pgsql-performance
hi Stephan, hi Tom,
sort_mem was at its default: 1024. I increased it, and the query takes
even longer (~ 36 secs). I tried two different values: 4096 and 8192,
this last time I reduced the shared_buffers to 25600 (--> ~ 37 secs).
Another point is: after a vacuum, the cost would slightly increase.
would it help to cluster the index? but as I am using several indexes I
find it difficult to decide on which index to cluster.
(I paste the output from vacuum full verbose analyze)
Thanks!
Chantal
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INFO: --Relation public.disease_occurrences_puid--
INFO: Pages 2079: Changed 0, reaped 0, Empty 0, New 0; Tup 471915: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
CPU 0.02s/0.05u sec elapsed 0.07 sec.
INFO: Index disease_occpd_puid_i: Pages 1036; Tuples 471915.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: Index disease_id_puid_uni: Pages 1297; Tuples 471915.
CPU 0.03s/0.05u sec elapsed 0.23 sec.
INFO: Rel disease_occurrences_puid: Pages: 2079 --> 2079; Tuple(s)
moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.disease_occurrences_puid
INFO: --Relation public.gene_occurrences_puid--
INFO: Pages 1495: Changed 0, reaped 0, Empty 0, New 0; Tup 339347: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO: Index gene_occpd_puid_i: Pages 746; Tuples 339347.
CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO: Index gene_id_puid_uni: Pages 934; Tuples 339347.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO: Rel gene_occurrences_puid: Pages: 1495 --> 1495; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.gene_occurrences_puid
INFO: --Relation public.disease--
INFO: Pages 1522: Changed 0, reaped 0, Empty 0, New 0; Tup 164597: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 232; Re-using: Free/Avail.
Space 56920/38388; EndEmpty/Avail. Pages 0/603.
CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO: Index disease_name_i: Pages 1076; Tuples 164597.
CPU 0.05s/0.02u sec elapsed 0.18 sec.
INFO: Index disease_pkey: Pages 364; Tuples 164597.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: Index disease_uni: Pages 1168; Tuples 164597.
CPU 0.08s/0.04u sec elapsed 0.22 sec.
INFO: Rel disease: Pages: 1522 --> 1521; Tuple(s) moved: 75.
CPU 0.00s/0.03u sec elapsed 0.04 sec.
INFO: Index disease_name_i: Pages 1077; Tuples 164597: Deleted 75.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: Index disease_pkey: Pages 364; Tuples 164597: Deleted 75.
CPU 0.01s/0.02u sec elapsed 0.02 sec.
INFO: Index disease_uni: Pages 1168; Tuples 164597: Deleted 75.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: --Relation pg_toast.pg_toast_7114632--
INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index pg_toast_7114632_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing public.disease
INFO: --Relation public.gene--
INFO: Pages 1566: Changed 0, reaped 0, Empty 0, New 0; Tup 218085: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 348; Re-using: Free/Avail.
Space 48692/25408; EndEmpty/Avail. Pages 0/365.
CPU 0.01s/0.04u sec elapsed 0.04 sec.
INFO: Index gene_pkey: Pages 481; Tuples 218085.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO: Index gene_uni: Pages 1038; Tuples 218085.
CPU 0.04s/0.01u sec elapsed 0.19 sec.
INFO: Index gene_name_uni: Pages 917; Tuples 218085.
CPU 0.06s/0.00u sec elapsed 0.15 sec.
INFO: Rel gene: Pages: 1566 --> 1564; Tuple(s) moved: 230.
CPU 0.01s/0.06u sec elapsed 0.11 sec.
INFO: Index gene_pkey: Pages 482; Tuples 218085: Deleted 230.
CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO: Index gene_uni: Pages 1041; Tuples 218085: Deleted 230.
CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO: Index gene_name_uni: Pages 918; Tuples 218085: Deleted 230.
CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO: --Relation pg_toast.pg_toast_7114653--
INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index pg_toast_7114653_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing public.gene
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] optimizing query
@ 2003-01-27 00:54 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2003-01-27 00:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chantal Ackermann <chantal.ackermann@biomax.de>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-general@postgresql.org; pgsql-performance
Tom Lane wrote:
> > postgresql.conf:
> > shared_buffers: 121600
> > max_connections: 64
> > max_fsm_relations = 200
> > max_fsm_pages = 40000
> > effective_cache_size = 8000
>
> Try increasing sort_mem.
>
> Also, I'd back off on shared_buffers if I were you. There's no evidence
> that values above a few thousand buy anything.
Increasing shared_buffers above several thousand will only be a win if
your entire working set will fit in the larger buffer pool, but didn't
in the previous size. If you working set is smaller or larger than
that, pushing it above several thousand isn't a win. Is that a more
definitive answer?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimizing query
@ 2010-10-07 09:37 Srikanth K <sreekanthkata@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Srikanth K @ 2010-10-07 09:37 UTC (permalink / raw)
To: pgsql-performance
Sorry to forget to give the postgres version as 8.1
On Thu, Oct 7, 2010 at 2:12 PM, Srikanth K <sreekanthkata@gmail.com> wrote:
> Hi Can u Please let me know how can i optimize this query better. As i am
> attaching u the Query, Schema and Explain Analyze Report.
>
> Plerase help me in optimizing this query.
>
> select
> s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as
> svcdescr from vwsubsmin s
> inner join packages p on s.svcno=p.pkgno
> inner join account a on a.actno=s.actno
> inner join ssgdom d on a.domno=d.domno
> inner join (select subsno from getexpiringsubs(1,cast('2' as
> integer),cast('3' as double precision),'4') as
> (subsno int,expirydt timestamp without time zone,balcpt double precision))
> as e on s.subsno=e.subsno
> where s.status <=15 and d.domno=5
> order by d.domname,s.expirydt,a.actname;
>
>
> --
> regards,
> Srikanth Kata
>
--
regards,
Srikanth Kata
^ permalink raw reply [nested|flat] 11+ messages in thread
* Optimizing query
@ 2010-11-24 14:48 pasman pasmański <pasman.p@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: pasman pasmański @ 2010-11-24 14:48 UTC (permalink / raw)
To: pgsql-performance
Hello.
I have a query which works a bit slow.
It's runned on desktop computer: AMD Athlon X2 2GHz , Win Xp sp2, 1GB ram.
Postgres 8.4.5 with some changes in config:
shared_buffers = 200MB # min 128kB
# (change requires restart)
temp_buffers = 8MB # min 800kB
work_mem = 12MB # min 64kB
maintenance_work_mem = 32MB # min 1MB
Indexes in table "NumeryA":
"NTA", "NKA", "KodBłędu", "Plik" primary key
"DataPliku", "KodBłędu" index dp_kb
"NKA", "NTA" index nka_nta
Indexes in table "Rejestr stacji do naprawy":
"LP" - primary key
"Numer kierunkowy", substr("Numer stacji"::text, 1, 5) - index "3"
"Data weryfikacji" - index "Data weryfikacji_1"
"Numer kierunkowy", "Numer stacji", "Data odrzucania bilingu z
Serat" - index "Powtórzenia"
---------------------
Query is:
----------------------
SELECT
A."NKA",
A."NTA",
Min("PołączeniaMin") || ',' || Max("PołączeniaMax") AS "Biling",
Sum("Ile")::text AS "Ilość CDR",
R."LP"::text AS "Sprawa",
(R."Osoba weryfikująca") AS "Osoba",
to_char(min("Wartość"),'FM9999990D00') AS "Wartość po kontroli",
max(R."Kontrola po naprawie w Serat - CDR")::text AS "CDR po kontroli",
min(A."KodBłędu")::text AS KodBłędu,
Max(to_char(R."Data kontroli",'YYYY-MM-DD')) AS "Ostatnia Kontrola"
, max("Skutek wprowadzenia błednej ewidencji w Serat") as "Skutek"
, sum(www.a_biling_070("NRB"))::text
, sum(www.a_biling_darmowy("NRB"))::text
FROM
(SELECT "NumeryA".*
FROM ONLY "NumeryA"
WHERE "DataPliku" >= current_date-4*30 and "KodBłędu"=74::text
) AS A
LEFT JOIN
(SELECT * FROM "Rejestr stacji do naprawy"
WHERE "Data weryfikacji" >= current_date-4*30
) AS R
ON
A."NKA" = R."Numer kierunkowy"
and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)
and A."NTA" like R."Numer stacji"
GROUP BY R."Osoba weryfikująca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000
----------------------
Explain analyze:
----------------------
"Limit (cost=30999.84..31012.34 rows=5000 width=149) (actual
time=7448.483..7480.094 rows=5000 loops=1)"
" -> Sort (cost=30999.84..31073.19 rows=29341 width=149) (actual
time=7448.475..7459.663 rows=5000 loops=1)"
" Sort Key: (sum("NumeryA"."Ile"))"
" Sort Method: top-N heapsort Memory: 1488kB"
" -> GroupAggregate (cost=11093.77..29050.46 rows=29341
width=149) (actual time=4700.654..7377.762 rows=14225 loops=1)"
" -> Sort (cost=11093.77..11167.12 rows=29341
width=149) (actual time=4699.587..4812.776 rows=46732 loops=1)"
" Sort Key: "Rejestr stacji do naprawy"."Osoba
weryfikująca", "Rejestr stacji do naprawy"."LP", "NumeryA"."NKA",
"NumeryA"."NTA""
" Sort Method: quicksort Memory: 9856kB"
" -> Merge Left Join (cost=8297.99..8916.58
rows=29341 width=149) (actual time=2931.449..3735.876 rows=46732
loops=1)"
" Merge Cond: ((("NumeryA"."NKA")::text =
("Rejestr stacji do naprawy"."Numer kierunkowy")::text) AND
((substr(("NumeryA"."NTA")::text, 1, 5)) = (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5))))"
" Join Filter: (("NumeryA"."NTA")::text ~~
("Rejestr stacji do naprawy"."Numer stacji")::text)"
" -> Sort (cost=6062.18..6135.53 rows=29341
width=95) (actual time=2131.297..2241.303 rows=46694 loops=1)"
" Sort Key: "NumeryA"."NKA",
(substr(("NumeryA"."NTA")::text, 1, 5))"
" Sort Method: quicksort Memory: 7327kB"
" -> Bitmap Heap Scan on "NumeryA"
(cost=1502.09..3884.98 rows=29341 width=95) (actual
time=282.570..1215.355 rows=46694 loops=1)"
" Recheck Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
" -> Bitmap Index Scan on dp_kb
(cost=0.00..1494.75 rows=29341 width=0) (actual time=281.991..281.991
rows=46694 loops=1)"
" Index Cond: (("DataPliku"
>= (('now'::text)::date - 120)) AND (("KodBłędu")::text =
'74'::text))"
" -> Sort (cost=2235.82..2285.03 rows=19684
width=64) (actual time=800.101..922.463 rows=54902 loops=1)"
" Sort Key: "Rejestr stacji do
naprawy"."Numer kierunkowy", (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5))"
" Sort Method: quicksort Memory: 3105kB"
" -> Seq Scan on "Rejestr stacji do
naprawy" (cost=0.00..831.88 rows=19684 width=64) (actual
time=2.118..361.463 rows=19529 loops=1)"
" Filter: ("Data weryfikacji" >=
(('now'::text)::date - 120))"
"Total runtime: 7495.697 ms"
---------------------------------
How to make it faster ?
------------
pasman
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimizing query
@ 2010-11-26 09:46 Pierre C <lists@peufeu.com>
parent: pasman pasmański <pasman.p@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Pierre C @ 2010-11-26 09:46 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, pasman pasmański <pasman.p@gmail.com>
Note that your LEFT JOIN condition is probably quite slow...
Please post EXPLAIN ANALYZE for this simplified version :
SELECT
R."Osoba weryfikuj?ca",
R."LP",
A."NKA",
A."NTA",
Sum("Ile")
FROM "NumeryA" A
LEFT JOIN "Rejestr stacji do naprawy" R ON (
A."NKA" = R."Numer kierunkowy"
and A."NTA" like R."Numer stacji"
and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)
)
WHERE
A."DataPliku" >= current_date-4*30
and A."KodB??du"=74::text
and R."Data weryfikacji" >= current_date-4*30
GROUP BY R."Osoba weryfikuj?ca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000
And also post EXPLAIN ANALYZE for this :
SELECT
A."NKA",
A."NTA",
Sum("Ile") AS ss -- if it's in this table
FROM "NumeryA" A
WHERE
A."DataPliku" >= current_date-4*30
and A."KodB??du"=74::text
GROUP BY A."NKA", A."NTA"
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimizing query
@ 2010-11-26 15:06 pasman pasmański <pasman.p@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: pasman pasmański @ 2010-11-26 15:06 UTC (permalink / raw)
To: pgsql-performance
Thanks for reply.
First query:
SELECT
R."Osoba weryfikująca" AS "Osoba",
R."LP"::text AS "Sprawa",
A."NKA",
A."NTA",
Sum(A."Ile")::text AS "Ilość CDR"
FROM
ONLY "NumeryA" A
LEFT JOIN
"Rejestr stacji do naprawy" R
ON
A."NKA" = R."Numer kierunkowy"
and A."NTA" like R."Numer stacji"
and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)
WHERE
"DataPliku" >= current_date-4*30
and "KodBłędu"=74::text
and "Data weryfikacji" >= current_date-4*30
GROUP BY R."Osoba weryfikująca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000
-----------------------
Explain analyze
-----------------------
"Limit (cost=8806.28..8806.30 rows=5 width=28) (actual
time=2575.143..2607.092 rows=5000 loops=1)"
" -> Sort (cost=8806.28..8806.30 rows=5 width=28) (actual
time=2575.135..2586.797 rows=5000 loops=1)"
" Sort Key: (sum(a."Ile"))"
" Sort Method: quicksort Memory: 929kB"
" -> HashAggregate (cost=8806.12..8806.23 rows=5 width=28)
(actual time=2500.549..2544.315 rows=9564 loops=1)"
" -> Merge Join (cost=8196.81..8806.04 rows=5 width=28)
(actual time=1583.222..2368.858 rows=37364 loops=1)"
" Merge Cond: (((a."NKA")::text = (r."Numer
kierunkowy")::text) AND ((substr((a."NTA")::text, 1, 5)) =
(substr((r."Numer stacji")::text, 1, 5))))"
" Join Filter: ((a."NTA")::text ~~ (r."Numer stacji")::text)"
" -> Sort (cost=5883.01..5952.95 rows=27977
width=15) (actual time=1006.220..1118.692 rows=46769 loops=1)"
" Sort Key: a."NKA", (substr((a."NTA")::text, 1, 5))"
" Sort Method: quicksort Memory: 4313kB"
" -> Bitmap Heap Scan on "NumeryA" a
(cost=1454.33..3816.64 rows=27977 width=15) (actual
time=16.331..158.007 rows=46769 loops=1)"
" Recheck Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
" -> Bitmap Index Scan on dp_kb
(cost=0.00..1447.34 rows=27977 width=0) (actual time=15.838..15.838
rows=46769 loops=1)"
" Index Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
" -> Sort (cost=2313.79..2364.81 rows=20410
width=24) (actual time=576.966..703.179 rows=56866 loops=1)"
" Sort Key: r."Numer kierunkowy",
(substr((r."Numer stacji")::text, 1, 5))"
" Sort Method: quicksort Memory: 1973kB"
" -> Seq Scan on "Rejestr stacji do naprawy"
r (cost=0.00..852.74 rows=20410 width=24) (actual time=0.050..143.901
rows=20768 loops=1)"
" Filter: ("Data weryfikacji" >=
(('now'::text)::date - 120))"
"Total runtime: 2620.220 ms"
---------------------------
Second query:
----------------------------
SELECT
A."NKA",
A."NTA",
Sum("Ile") AS ss -- if it's in this table
FROM "NumeryA" A
WHERE
A."DataPliku" >= current_date-4*30
and A."KodBłędu"=74::text
GROUP BY A."NKA", A."NTA"
--------------------------------
Explain analyze:
--------------------------------
"HashAggregate (cost=20616.64..20643.22 rows=2798 width=15) (actual
time=13244.712..13284.490 rows=14288 loops=1)"
" -> Append (cost=1454.33..20406.79 rows=27979 width=15) (actual
time=16.811..13093.395 rows=46769 loops=1)"
" -> Bitmap Heap Scan on "NumeryA" a (cost=1454.33..3816.64
rows=27977 width=15) (actual time=16.804..141.495 rows=46769 loops=1)"
" Recheck Cond: (("DataPliku" >= (('now'::text)::date -
120)) AND (("KodBłędu")::text = '74'::text))"
" -> Bitmap Index Scan on dp_kb (cost=0.00..1447.34
rows=27977 width=0) (actual time=16.289..16.289 rows=46769 loops=1)"
" Index Cond: (("DataPliku" >= (('now'::text)::date
- 120)) AND (("KodBłędu")::text = '74'::text))"
" -> Seq Scan on "NumeryA_2008" a (cost=0.00..16590.16 rows=2
width=15) (actual time=12759.731..12759.731 rows=0 loops=1)"
" Filter: ((("KodBłędu")::text = '74'::text) AND
("DataPliku" >= (('now'::text)::date - 120)))"
"Total runtime: 13314.149 ms"
The first query looks to work faster than original (6s) thanks !!! :)
------------
pasman
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-11-26 15:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-23 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 15:52 ` Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-27 00:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2010-10-07 09:37 Re: Optimizing query Srikanth K <sreekanthkata@gmail.com>
2010-11-24 14:48 Optimizing query pasman pasmański <pasman.p@gmail.com>
2010-11-26 09:46 ` Re: Optimizing query Pierre C <lists@peufeu.com>
2010-11-26 15:06 Re: Optimizing query pasman pasmański <pasman.p@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