pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Optimization
22+ messages / 18 participants
[nested] [flat]
* Query Optimization
@ 2004-12-14 21:34 sarlav kumar <sarlavk@yahoo.com>
0 siblings, 1 reply; 22+ messages in thread
From: sarlav kumar @ 2004-12-14 21:34 UTC (permalink / raw)
To: pgsql-performance; pgsql-novice@postgresql.org
Hi all,
Can someone please help me optimize this query? Is there a better way to write this query? I am generating a report of transactions ordered by time and with details of the sender and receiver etc.
SELECT distinct a.time::date ||'<br>'||substring(a.time::time::text,1,8) as Time,
CASE WHEN a.what = 0 THEN 'Money Transfer' WHEN a.what = 15 THEN 'Purchase' WHEN a.what = 26 THEN 'Merchant Streamline' WHEN a.what = 13 THEN 'Reversal' END as Transaction_Type ,
c1.account_no as SenderAccount, c2.account_no as RecieverAccount,
b.country as SenderCountry, d.country as RecieverCountry,
b.firstname as SenderFirstName, b.lastname as SenderLastName,
d.firstname as ReceiverFirstName, d.lastname as ReceiverLastName,
a.status as status,
(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 1 and td.uid = a.target_uid) as ReversedAmount,
(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 0 and td.uid = a.uid ) as DepositedAmount, a.flags, (a.amount * 0.01) as Amount,
(a.fee * 0.01) as Fee
FROM data a, customerdata b, customerdata d, customer c1, customer c2, participant p, data a2
WHERE p.id = a.partner_id AND (a.uid = c1.id) AND (a.target_uid = c2.id) and c1.id=b.uid and c2.id=d.uid
and a.confirmation is not null AND (a2.ref_id = a.id) and
((a2.what = 13) or (a2.what = 17) ) ORDER BY time desc ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Unique (cost=2978.27..2981.54 rows=8 width=150) (actual time=502.29..506.75 rows=382 loops=1)
-> Sort (cost=2978.27..2978.46 rows=77 width=150) (actual time=502.29..502.61 rows=461 loops=1)
Sort Key: ((((a."time")::date)::text || '<br>'::text) || "substring"(((a."time")::time without time zone)::text, 1, 8)), CASE WHEN (a
.what = 0) THEN 'Money Transfer'::text WHEN (a.what = 15) THEN 'Purchase'::text WHEN (a.what = 26) THEN 'Merchant Streamline'::text WHEN (a.wh
at = 13) THEN 'Reversal'::text ELSE NULL::text END, c1.account_no, c2.account_no, b.country, d.country, b.firstname, b.lastname, d.firstname,
d.lastname, a.status, (subplan), (subplan), a.flags, ((a.amount)::numeric * 0.01), ((a.fee)::numeric * 0.01)
-> Hash Join (cost=2687.00..2975.86 rows=77 width=150) (actual time=423.91..493.48 rows=461 loops=1)
Hash Cond: ("outer".partner_id = "inner".id)
-> Nested Loop (cost=2494.67..2781.99 rows=77 width=146) (actual time=413.19..441.61 rows=472 loops=1)
-> Merge Join (cost=2494.67..2526.04 rows=77 width=116) (actual time=413.09..429.86 rows=472 loops=1)
Merge Cond: ("outer".id = "inner".ref_id)
-> Sort (cost=1443.39..1458.57 rows=6069 width=108) (actual time=370.14..377.72 rows=5604 loops=1)
Sort Key: a.id
-> Hash Join (cost=203.50..1062.01 rows=6069 width=108) (actual time=20.35..335.44 rows=5604 loops=1)
Hash Cond: ("outer".uid = "inner".id)
-> Merge Join (cost=0.00..676.43 rows=6069 width=91) (actual time=0.42..255.33 rows=5611 loops=1)
Merge Cond: ("outer".target_uid = "inner".uid)
-> Merge Join (cost=0.00..1224.05 rows=6069 width=61) (actual time=0.34..156.74 rows=5611 loops
=1)
Merge Cond: ("outer".target_uid = "inner".id)
-> Index Scan using data_target_uid on data a (cost=0.00..2263.05 rows=6069 width=44) (ac
tual time=0.23..63.87 rows=5630 loops=1)
Filter: (confirmation IS NOT NULL)
-> Index Scan using customer_pkey on customer c2 (cost=0.00..631.03 rows=6120 width=17) (
actual time=0.05..50.97 rows=10862 loops=1)
-> Index Scan using customerdata_uid_idx on customerdata d (cost=0.00..312.36 rows=6085 width=3
0) (actual time=0.06..48.95 rows=10822 loops=1)
-> Hash (cost=188.20..188.20 rows=6120 width=17) (actual time=19.81..19.81 rows=0 loops=1)
-> Seq Scan on customer c1 (cost=0.00..188.20 rows=6120 width=17) (actual time=0.03..12.30 rows
=6157 loops=1)
-> Sort (cost=1051.28..1052.52 rows=497 width=8) (actual time=42.05..42.51 rows=542 loops=1)
Sort Key: a2.ref_id
-> Seq Scan on data a2 (cost=0.00..1029.00 rows=497 width=8) (actual time=0.21..41.14 rows=545 loops=1)
Filter: ((what = 13) OR (what = 17))
-> Index Scan using customerdata_uid_idx on customerdata b (cost=0.00..3.31 rows=1 width=30) (actual time=0.01..0.01 ro
ws=1 loops=472)
Index Cond: (b.uid = "outer".uid)
-> Hash (cost=192.26..192.26 rows=26 width=4) (actual time=10.50..10.50 rows=0 loops=1)
-> Seq Scan on participant p (cost=0.00..192.26 rows=26 width=4) (actual time=10.42..10.46 rows=26 loops=1)
SubPlan
-> Aggregate (cost=6.08..6.08 rows=1 width=4) (actual time=0.03..0.03 rows=1 loops=461)
-> Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.02..0.02 ro
ws=1 loops=461)
Index Cond: (data_id = $0)
Filter: ((dir = 1) AND (uid = $1))
-> Aggregate (cost=6.08..6.08 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=461)
-> Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.01..0.01 ro
ws=1 loops=461)
Index Cond: (data_id = $0)
Filter: ((dir = 0) AND (uid = $2))
Total runtime: 508.27 msec
(40 rows)
Time: 528.13 ms
Please help me out.
Thanks in advance!
Saranya
---------------------------------
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [PERFORM] Query Optimization
@ 2004-12-15 18:05 Andrew Lazarus <andrew@pillette.com>
parent: sarlav kumar <sarlavk@yahoo.com>
0 siblings, 0 replies; 22+ messages in thread
From: Andrew Lazarus @ 2004-12-15 18:05 UTC (permalink / raw)
To: sarlav kumar <sarlavk@yahoo.com>; +Cc: pgsql-performance; pgsql-novice@postgresql.org
sarlav kumar wrote:
> Hi all,
>
> Can someone please help me optimize this query? Is there a better way to
> write this query? I am generating a report of transactions ordered by
> time and with details of the sender and receiver etc.
>
> SELECT distinct a.time::date ||'<br>'||substring(a.time::time::text,1,8)
> as Time,
> CASE WHEN a.what = 0 THEN 'Money Transfer' WHEN a.what = 15 THEN
> 'Purchase' WHEN a.what = 26 THEN 'Merchant Streamline' WHEN a.what = 13
> THEN 'Reversal' END as Transaction_Type ,
> c1.account_no as SenderAccount, c2.account_no as RecieverAccount,
> b.country as SenderCountry, d.country as RecieverCountry,
> b.firstname as SenderFirstName, b.lastname as SenderLastName,
> d.firstname as ReceiverFirstName, d.lastname as ReceiverLastName,
> a.status as status,
> (select sum(td.amount * 0.01) from transaction_data td where td.data_id
> = a2.id and td.dir = 1 and td.uid = a.target_uid) as ReversedAmount,
> (select sum(td.amount * 0.01) from transaction_data td where td.data_id
> = a2.id and td.dir = 0 and td.uid = a.uid ) as DepositedAmount, a.flags,
> (a.amount * 0.01) as Amount,
> (a.fee * 0.01) as Fee
> FROM data a, customerdata b, customerdata d, customer c1, customer c2 ,
> participant p, data a2
> WHERE p.id = a.partner_id AND (a.uid = c1.id) AND (a.target_uid = c2.id)
> and c1.id=b.uid and c2.id=d.uid
> and a.confirmation is not null AND (a2.ref_id = a.id) and
> ((a2.what = 13) or (a2.what = 17) ) ORDER BY time desc ;
(query plan followed)
The expensive operation is the UNIQUE. Are you sure, in terms of
business logic, that this is necessary? Is it actually possible to have
duplicate transactions at the exact same time, and if so, would you
really want to eliminate them?
As an aside, I prefer to have numeric constants like the 'what' field in
a small lookup table of two columns (what_code, what_description); it's
easier to extend and to document.
Attachments:
andrew.vcf (text/x-vcard, 296B) download
begin:vcard
fn:Andrew Lazarus
n:Lazarus;Andrew
org:Pillette Investment Management;Research and Development
adr;dom:;;3028 Fillmore;San Francisco;CA;94123
email;internet:andrew@pillette.com
title:Director
tel;work:800-366-0688
tel;fax:415-440-4093
url:http://www.pillette.com
version:2.1
end:vcard
* Query Optimization
@ 2005-03-09 00:04 James G Wilkinson <jgw@alpinegeophysics.com>
0 siblings, 1 reply; 22+ messages in thread
From: James G Wilkinson @ 2005-03-09 00:04 UTC (permalink / raw)
To: pgsql-performance
All,
I hope that this is the right place to post. I am relatively new to
PostgreSQL (i.e., < 1 year in coding) and am just starting to
delve into the issues of query optimization. I have hunted around
the web for the basics of query optimization, but I have not had
much success in interpreting the documents. I have also been
trying to learn the basics of the EXPLAIN command....also without
much success, but I will keep trying.
Anyway, here is what the system reports on the following command:
EXPLAIN SELECT a.country_code, a.state_county_fips,
icell, jcell, a.beld3_species_id, pollutant_code,
SUM(b.ratio * d.emissions_factor * a.percent_ag *
e.ag_fraction * 10000) as normalized_emissions
FROM "globals"."biogenic_beld3_data" a,
"spatial"."tmpgrid" b,
"globals"."biogenic_emissions_factors" d,
"globals"."biogenic_beld3_ag_data" e
WHERE a.beld3_icell=b.b_icell AND
a.beld3_jcell=b.b_jcell AND
a.country_code=e.country_code AND
a.state_county_fips=e.state_county_fips AND
a.beld3_species_id=d.beld3_species_id AND
a.ag_forest_records > 0 AND
a.percent_ag > 0 AND d.emissions_factor > 0
GROUP BY a.country_code, a.state_county_fips, icell, jcell,
a.beld3_species_id, pollutant_code
ORDER BY a.country_code, a.state_county_fips, icell, jcell,
a.beld3_species_id, pollutant_code;
QUERY
PLAN
-----------------------------------------------------------------------------------------------
GroupAggregate (cost=65034.94..71110.50 rows=151889 width=73)
->Sort (cost=65034.94..65414.66 rows=151889 width=73)
Sort Key: a.country_code, a.state_county_fips, b.icell, b.jcell,
a.beld3_species_id, d.pollutant_code
->Hash Join (cost=33749.64..37412.88 rows=151889 width=73)
Hash Cond: ("outer".beld3_species_id = "inner".beld3_species_id)
->Merge Join (cost=33728.84..35303.61 rows=37972 width=56)
Merge Cond: ((("outer".country_code)::text =
"inner"."?column8?") AND
(("outer".state_county_fips)::text =
"inner"."?column9?"))
->Index Scan using biogenic_beld3_ag_data_pk on
biogenic_beld3_ag_data e
(cost=0.00..806.68 rows=20701 width=26)
->Sort (cost=33728.84..33741.67 rows=5131 width=45)
Sort Key: (a.country_code)::text,
(a.state_county_fips)::text
->Nested Loop (cost=0.00..33412.65 rows=5131 width=45)
->Seq Scan on biogenic_beld3_data a
(cost=0.00..3593.02 rows=5637 width=37)
Filter: ((ag_forest_records > 0) AND (percent_ag
> 0::numeric))
->Index Scan using tmpgrid_pk on tmpgrid b
(cost=0.00..5.27 rows=1 width=24)
Index Cond: ((b.b_icell = "outer".beld3_icell) AND
(b.b_jcell = "outer".beld3_jcell))
->Hash (cost=18.50..18.50 rows=920 width=21)
->Seq Scan on biogenic_emissions_factors d
(cost=0.00..18.50 rows=920 width=21)
Filter: (emissions_factor > 0::numeric)
(18 rows)
Firstly, I am frankly mystified on how to interpret all this. If anyone
could point me to a document or two that will help me decipher this,
I will greatly appreciate it.
Secondly, I have figured out that SEQ SCANs are typically bad. I am
concerned that a SEQ SCAN is being performed on 'biogenic_beld3_data'
which is the largest table in the query. I would rather have a SEQ SCAN
be performed on 'tmpgrid' which contains the keys that subset the data
from 'biogenic_beld3_data.' Is this naive on my part?
Thirdly, I have run EXPLAIN on other queries that report back a
GroupAggregate Cost=<low 300,000s> that runs in about 30 minutes
on my relatively highend linux machine. But when I run this particular
query, it takes on the order of 90 minutes to complete. Any thoughts
on why this happens will be appreciated.
Finally, if anyone can be so kind as to provide insight on how to better
optimize this query, I will, again, be deeply grateful.
Thanks in advance.
terrakit
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query Optimization
@ 2005-03-09 01:46 John A Meinel <john@arbash-meinel.com>
parent: James G Wilkinson <jgw@alpinegeophysics.com>
0 siblings, 0 replies; 22+ messages in thread
From: John A Meinel @ 2005-03-09 01:46 UTC (permalink / raw)
To: James G Wilkinson <jgw@alpinegeophysics.com>; +Cc: pgsql-performance
James G Wilkinson wrote:
> All,
>
...
> Firstly, I am frankly mystified on how to interpret all this. If anyone
> could point me to a document or two that will help me decipher this,
> I will greatly appreciate it.
>
I assume you have looked at:
http://www.postgresql.org/docs/8.0/static/performance-tips.html
And didn't find it helpful enough. I'm not really sure what help you are
asking. Are you saying that this query is performing slowly and you want
to speed it up? Or you just want to understand how to interpret the
output of explain?
> Secondly, I have figured out that SEQ SCANs are typically bad. I am
> concerned that a SEQ SCAN is being performed on 'biogenic_beld3_data'
> which is the largest table in the query. I would rather have a SEQ SCAN
> be performed on 'tmpgrid' which contains the keys that subset the data
> from 'biogenic_beld3_data.' Is this naive on my part?
It depends how much data is being extracted. If you have 1,000,000 rows,
and only need 10, then an index scan is wonderful. If you need 999,999,
then a sequential scan is much better (the break even point is <10%)
From the explain, it thinks it is going to be needing 5,637 rows from
biogenic_beld3_data, what is that portion relative to the total?
The values at least look like you've run vacuum analyze. Have you tried
running "explain analyze" instead of just explain? Then you can see if
the planners estimates are accurate.
If you want some help to force it, you could try a subselect query.
Something like:
select * from biogenic_beld3_data b where b.beld3_icell = (select
b_icell from tmpgrid_pk) and b.beld3_jcell = (select b_jcell from
tmpgrid_pk);
>
> Thirdly, I have run EXPLAIN on other queries that report back a
> GroupAggregate Cost=<low 300,000s> that runs in about 30 minutes
> on my relatively highend linux machine. But when I run this particular
> query, it takes on the order of 90 minutes to complete. Any thoughts
> on why this happens will be appreciated.
>
Remember cost is in terms of page fetches, not in seconds.
Probably it is just an issue of postgres mis-estimating the selectivity
of one of your queries.
Also, you have a fairly complex SUM occurring involving 4
multiplications on an estimated 150,000 rows. While doesn't seem like it
should take 90 minutes, it also isn't a trivial operation.
> Finally, if anyone can be so kind as to provide insight on how to better
> optimize this query, I will, again, be deeply grateful.
>
> Thanks in advance.
>
> terrakit
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Query Optimization
@ 2007-02-19 17:03 Reinhard Vicinus <rvicinus@rjm.de>
0 siblings, 2 replies; 22+ messages in thread
From: Reinhard Vicinus @ 2007-02-19 17:03 UTC (permalink / raw)
To: pgsql-performance
PostgreSQL version: 8.1.6
OS: Debian etch
The following query needs a lot of time because the query planner
reorders the joins:
select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
join dbmail_headername n ON v.headername_id=n.id
where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
and lower(n.headername) IN
('from','to','cc','subject','date','message-id',
'priority','x-priority','references','newsgroups','in-reply-to',
'content-type','x-spam-status','x-spam-flag');
If I prevent the query planner from reordering the joins with 'set
join_collapse_limit=1;' then the same query is faster. At the end of the
Mail is the output of a explain analyze for both cases.
The statistics of the database are updated each night. Is there an error
(in the statistical data) which is responsible for the reordering of the
joins? And if not are there other alternatives for preventing join
reordering?
Thanks
Reinhard
Explain analyze with set join_collapse_limit=8:
Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
time=82677.950..89103.192 rows=2699 loops=1)
Merge Cond: ("outer".physmessage_id = "inner".physmessage_id)
-> Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
time=247.238..247.578 rows=373 loops=1)
Sort Key: m.physmessage_id
-> Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
Recheck Cond: (mailbox_idnr = 1022)
Filter: ((message_idnr >= 698928) AND (message_idnr <=
1496874))
-> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
loops=1)
Index Cond: (mailbox_idnr = 1022)
-> Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
time=80156.731..85760.186 rows=3278076 loops=1)
Sort Key: v.physmessage_id
-> Hash Join (cost=51.00..285787.17 rows=533108 width=127)
(actual time=34.519..28260.855 rows=3370242 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Seq Scan on dbmail_headervalue v
(cost=0.00..241200.39 rows=7840939 width=115) (actual
time=0.006..16844.479 rows=7854485 loops=1)
-> Hash (cost=50.72..50.72 rows=113 width=28) (actual
time=34.493..34.493 rows=35 loops=1)
-> Bitmap Heap Scan on dbmail_headername n
(cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
rows=35 loops=1)
Recheck Cond: ((lower((headername)::text) =
'from'::text) OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
(lower((headername)::text) = 'message-id'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'references'::text) OR
(lower((headername)::text) = 'newsgroups'::text) OR
(lower((headername)::text) = 'in-reply-to'::text) OR
(lower((headername)::text) = 'content-type'::text) OR
(lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
dername)::text) = 'x-spam-flag'::text))
-> BitmapOr (cost=28.44..28.44 rows=116
width=0) (actual time=11.786..11.786 rows=0 loops=1)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.037..0.037 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'from'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'cc'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'subject'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'date'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=4 loops=1)
Index Cond:
(lower((headername)::text) = 'message-id'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=2 loops=1)
Index Cond:
(lower((headername)::text) = 'priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.032..0.032 rows=4 loops=1)
Index Cond:
(lower((headername)::text) = 'x-priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.015..0.015 rows=1 loops=1)
Index Cond:
(lower((headername)::text) = 'references'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.009..0.009 rows=0 loops=1)
Index Cond:
(lower((headername)::text) = 'newsgroups'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'in-reply-to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=1 loops=1)
Index Cond:
(lower((headername)::text) = 'content-type'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=11.549..11.549 rows=2 loops=1)
Index Cond:
(lower((headername)::text) = 'x-spam-status'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'x-spam-flag'::text)
Total runtime: 89277.937 ms
(47 rows)
Explain analyze with set join_collapse_limit=1:
Hash Join (cost=51.00..1607155.00 rows=821 width=127) (actual
time=14.640..47.851 rows=2699 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Nested Loop (cost=0.00..1607035.43 rows=12071 width=115) (actual
time=0.085..25.057 rows=7025 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx on
dbmail_messages m (cost=0.00..3515.08 rows=632 width=16) (actual
time=0.064..1.070 rows=373 loops=1)
Index Cond: (mailbox_idnr = 1022)
Filter: ((message_idnr >= 698928) AND (message_idnr <=
1496874))
-> Index Scan using dbmail_headervalue_physmsg_id on
dbmail_headervalue v (cost=0.00..2526.34 rows=870 width=115) (actual
time=0.010..0.035 rows=19 loops=373)
Index Cond: (v.physmessage_id = "outer".physmessage_id)
-> Hash (cost=50.72..50.72 rows=113 width=28) (actual
time=14.540..14.540 rows=35 loops=1)
-> Bitmap Heap Scan on dbmail_headername n (cost=28.44..50.72
rows=113 width=28) (actual time=14.429..14.492 rows=35 loops=1)
Recheck Cond: ((lower((headername)::text) = 'from'::text)
OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
(lower((headername)::text) = 'message-id'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'references'::text) OR
(lower((headername)::text) = 'newsgroups'::text) OR
(lower((headername)::text) = 'in-reply-to'::text) OR
(lower((headername)::text) = 'content-type'::text) OR
(lower((headername)::text) = 'x-spam-status'::text) OR
(lower((headername)::text) = 'x-spam-flag'::text))
-> BitmapOr (cost=28.44..28.44 rows=116 width=0)
(actual time=14.418..14.418 rows=0 loops=1)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=14.197..14.197 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'from'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'cc'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'subject'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'date'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=4 loops=1)
Index Cond: (lower((headername)::text) =
'message-id'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.011..0.011 rows=2 loops=1)
Index Cond: (lower((headername)::text) =
'priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.031..0.031 rows=4 loops=1)
Index Cond: (lower((headername)::text) =
'x-priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (lower((headername)::text) =
'references'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (lower((headername)::text) =
'newsgroups'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'in-reply-to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (lower((headername)::text) =
'content-type'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.028..0.028 rows=2 loops=1)
Index Cond: (lower((headername)::text) =
'x-spam-status'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.018..0.018 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'x-spam-flag'::text)
Total runtime: 49.634 ms
(41 rows)
--
Reinhard Vicinus
rjm business solutions GmbH
Sperlingweg 3,
68623 Lampertheim
Tel. 06206 9513084
Fax 06206 910315
--
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query Optimization
@ 2007-02-20 04:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Reinhard Vicinus <rvicinus@rjm.de>
1 sibling, 0 replies; 22+ messages in thread
From: Tom Lane @ 2007-02-20 04:54 UTC (permalink / raw)
To: Reinhard Vicinus <rvicinus@rjm.de>; +Cc: pgsql-performance
Reinhard Vicinus <rvicinus@rjm.de> writes:
> PostgreSQL version: 8.1.6
> The following query needs a lot of time because the query planner
> reorders the joins:
Try reducing random_page_cost, increasing effective_cache_size, and/or
updating to PG 8.2. Any of these are likely to make it like the
nestloop plan better...
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query Optimization
@ 2007-02-20 16:45 Jim C. Nasby <jim@nasby.net>
parent: Reinhard Vicinus <rvicinus@rjm.de>
1 sibling, 0 replies; 22+ messages in thread
From: Jim C. Nasby @ 2007-02-20 16:45 UTC (permalink / raw)
To: Reinhard Vicinus <rvicinus@rjm.de>; +Cc: pgsql-performance
It's not necessarily the join order that's an issue; it could also be
due to the merge join that it does in the first case. I've also run into
situations where the cost estimate for a merge join is way off the mark.
Rather than forcing the join order, you might try setting
enable_mergejoin=false.
On Mon, Feb 19, 2007 at 06:03:22PM +0100, Reinhard Vicinus wrote:
> PostgreSQL version: 8.1.6
> OS: Debian etch
>
> The following query needs a lot of time because the query planner
> reorders the joins:
>
> select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
> join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
> join dbmail_headername n ON v.headername_id=n.id
> where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
> and lower(n.headername) IN
> ('from','to','cc','subject','date','message-id',
> 'priority','x-priority','references','newsgroups','in-reply-to',
> 'content-type','x-spam-status','x-spam-flag');
>
> If I prevent the query planner from reordering the joins with 'set
> join_collapse_limit=1;' then the same query is faster. At the end of the
> Mail is the output of a explain analyze for both cases.
>
> The statistics of the database are updated each night. Is there an error
> (in the statistical data) which is responsible for the reordering of the
> joins? And if not are there other alternatives for preventing join
> reordering?
>
> Thanks
> Reinhard
>
>
>
> Explain analyze with set join_collapse_limit=8:
>
> Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
> time=82677.950..89103.192 rows=2699 loops=1)
> Merge Cond: ("outer".physmessage_id = "inner".physmessage_id)
> -> Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
> time=247.238..247.578 rows=373 loops=1)
> Sort Key: m.physmessage_id
> -> Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
> rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
> Recheck Cond: (mailbox_idnr = 1022)
> Filter: ((message_idnr >= 698928) AND (message_idnr <=
> 1496874))
> -> Bitmap Index Scan on dbmail_messages_8
> (cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
> loops=1)
> Index Cond: (mailbox_idnr = 1022)
> -> Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
> time=80156.731..85760.186 rows=3278076 loops=1)
> Sort Key: v.physmessage_id
> -> Hash Join (cost=51.00..285787.17 rows=533108 width=127)
> (actual time=34.519..28260.855 rows=3370242 loops=1)
> Hash Cond: ("outer".headername_id = "inner".id)
> -> Seq Scan on dbmail_headervalue v
> (cost=0.00..241200.39 rows=7840939 width=115) (actual
> time=0.006..16844.479 rows=7854485 loops=1)
> -> Hash (cost=50.72..50.72 rows=113 width=28) (actual
> time=34.493..34.493 rows=35 loops=1)
> -> Bitmap Heap Scan on dbmail_headername n
> (cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
> rows=35 loops=1)
> Recheck Cond: ((lower((headername)::text) =
> 'from'::text) OR (lower((headername)::text) = 'to'::text) OR
> (lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
> 'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
> (lower((headername)::text) = 'message-id'::text) OR
> (lower((headername)::text) = 'priority'::text) OR
> (lower((headername)::text) = 'x-priority'::text) OR
> (lower((headername)::text) = 'references'::text) OR
> (lower((headername)::text) = 'newsgroups'::text) OR
> (lower((headername)::text) = 'in-reply-to'::text) OR
> (lower((headername)::text) = 'content-type'::text) OR
> (lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
> dername)::text) = 'x-spam-flag'::text))
> -> BitmapOr (cost=28.44..28.44 rows=116
> width=0) (actual time=11.786..11.786 rows=0 loops=1)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.037..0.037 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'from'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'cc'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'subject'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'date'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=4 loops=1)
> Index Cond:
> (lower((headername)::text) = 'message-id'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=2 loops=1)
> Index Cond:
> (lower((headername)::text) = 'priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.032..0.032 rows=4 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.015..0.015 rows=1 loops=1)
> Index Cond:
> (lower((headername)::text) = 'references'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.009..0.009 rows=0 loops=1)
> Index Cond:
> (lower((headername)::text) = 'newsgroups'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'in-reply-to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=1 loops=1)
> Index Cond:
> (lower((headername)::text) = 'content-type'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=11.549..11.549 rows=2 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-spam-status'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-spam-flag'::text)
> Total runtime: 89277.937 ms
> (47 rows)
>
>
>
> Explain analyze with set join_collapse_limit=1:
>
> Hash Join (cost=51.00..1607155.00 rows=821 width=127) (actual
> time=14.640..47.851 rows=2699 loops=1)
> Hash Cond: ("outer".headername_id = "inner".id)
> -> Nested Loop (cost=0.00..1607035.43 rows=12071 width=115) (actual
> time=0.085..25.057 rows=7025 loops=1)
> -> Index Scan using dbmail_messages_mailbox_idx on
> dbmail_messages m (cost=0.00..3515.08 rows=632 width=16) (actual
> time=0.064..1.070 rows=373 loops=1)
> Index Cond: (mailbox_idnr = 1022)
> Filter: ((message_idnr >= 698928) AND (message_idnr <=
> 1496874))
> -> Index Scan using dbmail_headervalue_physmsg_id on
> dbmail_headervalue v (cost=0.00..2526.34 rows=870 width=115) (actual
> time=0.010..0.035 rows=19 loops=373)
> Index Cond: (v.physmessage_id = "outer".physmessage_id)
> -> Hash (cost=50.72..50.72 rows=113 width=28) (actual
> time=14.540..14.540 rows=35 loops=1)
> -> Bitmap Heap Scan on dbmail_headername n (cost=28.44..50.72
> rows=113 width=28) (actual time=14.429..14.492 rows=35 loops=1)
> Recheck Cond: ((lower((headername)::text) = 'from'::text)
> OR (lower((headername)::text) = 'to'::text) OR
> (lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
> 'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
> (lower((headername)::text) = 'message-id'::text) OR
> (lower((headername)::text) = 'priority'::text) OR
> (lower((headername)::text) = 'x-priority'::text) OR
> (lower((headername)::text) = 'references'::text) OR
> (lower((headername)::text) = 'newsgroups'::text) OR
> (lower((headername)::text) = 'in-reply-to'::text) OR
> (lower((headername)::text) = 'content-type'::text) OR
> (lower((headername)::text) = 'x-spam-status'::text) OR
> (lower((headername)::text) = 'x-spam-flag'::text))
> -> BitmapOr (cost=28.44..28.44 rows=116 width=0)
> (actual time=14.418..14.418 rows=0 loops=1)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=14.197..14.197 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'from'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.015..0.015 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'cc'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'subject'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'date'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=4 loops=1)
> Index Cond: (lower((headername)::text) =
> 'message-id'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.011..0.011 rows=2 loops=1)
> Index Cond: (lower((headername)::text) =
> 'priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.031..0.031 rows=4 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=1 loops=1)
> Index Cond: (lower((headername)::text) =
> 'references'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: (lower((headername)::text) =
> 'newsgroups'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'in-reply-to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=1 loops=1)
> Index Cond: (lower((headername)::text) =
> 'content-type'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.028..0.028 rows=2 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-spam-status'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.018..0.018 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-spam-flag'::text)
> Total runtime: 49.634 ms
> (41 rows)
>
> --
> Reinhard Vicinus
> rjm business solutions GmbH
> Sperlingweg 3,
> 68623 Lampertheim
> Tel. 06206 9513084
> Fax 06206 910315
> --
>
>
> ---------------------------(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
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 22+ messages in thread
* Query optimization....
@ 2007-07-31 01:08 Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 22+ messages in thread
From: Karl Denninger @ 2007-07-31 01:08 UTC (permalink / raw)
To: pgsql-performance
In a followup to a question I put forward here on performance which I
traced to the "stats" bug (and fixed it). Now I'm trying to optimize
that query and....... I'm getting confused fast...
I have the following (fairly complex) statement which is run with some
frequency:
select post.forum, post.subject, post.replied from post where toppost =
1 and (replied > (select lastview from forumlog where login='someone'
and forum=post.forum and number is null)) is not false AND (replied >
(select lastview from forumlog where login='someone' and
forum=post.forum and number=post.number)) is not false order by pinned
desc, replied desc;
This gives me exactly what I'm looking for BUT can be quite slow.
The "forumlog" table has one tuple for each post and user; it has the
fields "forum", "number", "login" and "lastview". The "post" items have
a "forum", "number" and "replied" field (which is used to match the
"lastview" one.)
When you look at a "post" (which may have replies) the application
updates your existing entry in that table if there is one, or INSERTs a
new tuple if not.
Therefore, for each post you have viewed, there is a tuple in the
"forumlog" table which represents the last time you looked at that item.
The problem is that for a person who has NOT visited a specific thread
of discussion, there is no "forumlog" entry for that person and post in
the table. Thus, to get all posts which (1) you've not seen at all, or
(2) you've seen but someone has added to since you saw them, the above
complex query is what I've come up with; there may be a "null" table
entry which a "wildcard" match if its present - if there is no match
then the item also must treated as new. The above statement works - but
its slow.
The following query is VERY fast but only returns those in which there
IS an entry in the table (e.g. you've visited the item at least once)
select post.forum, post.subject, post.replied from post, forumlog where
post.number = forumlog.number and post.toppost = 1 and post.replied >
forumlog.lastview and forumlog.login='someone' order by pinned desc,
replied desc;
What I haven't been able to figure out is how to structure a query that
is both fast and will return the posts for which you DO NOT have a
matching entry in the "forumlog" table for the specific post but DO
either (1) match the "null" number entry (that is, they're posted later
than that) OR (2) have no match at all. (The first statement matches
these other two cases)
Any ideas? (Its ok if that query(s) are separate; in other words, its
cool if I have to execute two or even three queries and get the results
separately - in fact, that might be preferrable in some circumstances)
Ideas?
--
Karl Denninger (karl@denninger.net)
http://www.denninger.net
%SPAMBLOCK-SYS: Matched [@postgresql.org+], message ok
^ permalink raw reply [nested|flat] 22+ messages in thread
* Query Optimization
@ 2008-03-26 18:36 Gopinath Narasimhan <gopinaras@yahoo.com>
0 siblings, 0 replies; 22+ messages in thread
From: Gopinath Narasimhan @ 2008-03-26 18:36 UTC (permalink / raw)
To: pgsql-performance
Dear Sirs,
I am doing this project of optimizing pg-sql query engine with compressed annealing. I would like to know if any deficiency in existing GEQO. If there are any TODO items remaining in GEQO kindly brief about the same. Awaiting discussions on this.
GN
---------------------------------
Never miss a thing. Make Yahoo your homepage.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Query optimization
@ 2008-11-30 18:45 tmp <skrald@amossen.dk>
0 siblings, 2 replies; 22+ messages in thread
From: tmp @ 2008-11-30 18:45 UTC (permalink / raw)
To: pgsql-performance
I am struggeling with the following query which fetches a random subset
of 200 questions that matches certain tags within certain languages.
However, the query takes forever to evaluate, even though I have a
"limit 200" appended. Any ideas on how to optimize it?
QUERY: ================================================
SELECT distinct q.question_id
FROM question_tags qt, questions q
WHERE q.question_id = qt.question_id
AND q.STATUS = 1
AND not q.author_id = 105
AND ((qt.language_id = 5 and qt.tag_id in
(1101,917,734,550,367,183)) or (qt.language_id = 4 and qt.tag_id in
(856,428)) or (qt.language_id =
3 and qt.tag_id in (1156,1028,899,771,642,514,385,257,128)) or
(qt.language_id = 2 and qt.tag_id in
(1193,1101,1009,917,826,734,642,550,458,367,275,183,91)))
and q.question_id not in (413)
LIMIT 200
EXPLAIN ANALYZE: =========================================
Limit (cost=1.50..1267.27 rows=200 width=4) (actual
time=278.169..880.934 rows=200 loops=1)
-> Unique (cost=1.50..317614.50 rows=50185 width=4) (actual
time=278.165..880.843 rows=200 loops=1)
-> Merge Join (cost=1.50..317489.04 rows=50185 width=4)
(actual time=278.162..880.579 rows=441 loops=1)
Merge Cond: (qt.question_id = q.question_id)
-> Index Scan using question_tags_question_id on
question_tags qt (cost=0.00..301256.96 rows=82051 width=4) (actual
time=24.171..146.811 rows=6067 loops=1)
Filter: (((language_id = 5) AND (tag_id = ANY
('{1101,917,734,550,367,183}'::integer[]))) OR ((language_id = 4) AND
(tag_id = ANY ('{856,428}'::integer[]))) OR ((language_id = 3) AND
(tag_id = ANY ('{1156,1028,899,771,642,514,385,257,128}'::integer[])))
OR ((language_id = 2) AND (tag_id = ANY
('{1193,1101,1009,917,826,734,642,550,458,367,275,183,91}'::integer[]))))
-> Index Scan using questions_pkey on questions q
(cost=0.00..15464.12 rows=83488 width=4) (actual time=222.956..731.737
rows=1000 loops=1)
Filter: ((q.author_id <> 105) AND (q.question_id
<> 413) AND (q.status = 1))
Total runtime: 881.152 ms
(9 rows)
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query optimization
@ 2008-11-30 19:37 Marc Cousin <cousinmarc@gmail.com>
parent: tmp <skrald@amossen.dk>
1 sibling, 0 replies; 22+ messages in thread
From: Marc Cousin @ 2008-11-30 19:37 UTC (permalink / raw)
To: pgsql-performance
Le Sunday 30 November 2008 19:45:11 tmp, vous avez écrit :
> I am struggeling with the following query which fetches a random subset
> of 200 questions that matches certain tags within certain languages.
> However, the query takes forever to evaluate, even though I have a
> "limit 200" appended. Any ideas on how to optimize it?
>
> QUERY: ================================================
>
> SELECT distinct q.question_id
> FROM question_tags qt, questions q
> WHERE q.question_id = qt.question_id
> AND q.STATUS = 1
> AND not q.author_id = 105
> AND ((qt.language_id = 5 and qt.tag_id in
> (1101,917,734,550,367,183)) or (qt.language_id = 4 and qt.tag_id in
> (856,428)) or (qt.language_id =
> 3 and qt.tag_id in (1156,1028,899,771,642,514,385,257,128)) or
> (qt.language_id = 2 and qt.tag_id in
> (1193,1101,1009,917,826,734,642,550,458,367,275,183,91)))
> and q.question_id not in (413)
> LIMIT 200
>
> EXPLAIN ANALYZE: =========================================
>
> Limit (cost=1.50..1267.27 rows=200 width=4) (actual
> time=278.169..880.934 rows=200 loops=1)
> -> Unique (cost=1.50..317614.50 rows=50185 width=4) (actual
> time=278.165..880.843 rows=200 loops=1)
> -> Merge Join (cost=1.50..317489.04 rows=50185 width=4)
> (actual time=278.162..880.579 rows=441 loops=1)
> Merge Cond: (qt.question_id = q.question_id)
> -> Index Scan using question_tags_question_id on
> question_tags qt (cost=0.00..301256.96 rows=82051 width=4) (actual
> time=24.171..146.811 rows=6067 loops=1)
> Filter: (((language_id = 5) AND (tag_id = ANY
> ('{1101,917,734,550,367,183}'::integer[]))) OR ((language_id = 4) AND
> (tag_id = ANY ('{856,428}'::integer[]))) OR ((language_id = 3) AND
> (tag_id = ANY ('{1156,1028,899,771,642,514,385,257,128}'::integer[])))
> OR ((language_id = 2) AND (tag_id = ANY
> ('{1193,1101,1009,917,826,734,642,550,458,367,275,183,91}'::integer[]))))
> -> Index Scan using questions_pkey on questions q
> (cost=0.00..15464.12 rows=83488 width=4) (actual time=222.956..731.737
> rows=1000 loops=1)
> Filter: ((q.author_id <> 105) AND (q.question_id
> <> 413) AND (q.status = 1))
> Total runtime: 881.152 ms
> (9 rows)
First, because of the distinct, the limit 200 wont reduce the work to be done
a lot : it will still have to sort for the unique. Only when everything is
sorted will it take only the first 200 records. And anyhow it seems there are
only 441 rows before doing the distinct, so, at least for this query, it
won't change a lot the times.
Then it seems to me that you may try to create composed indexes, because there
is a lot of filtering after the index scans (that is if you want the query to
be faster).
Maybe (q.author_id,q.status).
For qt you may try (qt.language_id,qt.tag_id)...
Hope it helps
Cheers
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query optimization
@ 2008-12-01 13:24 PFC <lists@peufeu.com>
parent: tmp <skrald@amossen.dk>
1 sibling, 0 replies; 22+ messages in thread
From: PFC @ 2008-12-01 13:24 UTC (permalink / raw)
To: tmp <skrald@amossen.dk>; pgsql-performance
> I am struggeling with the following query which fetches a random subset
> of 200 questions that matches certain tags within certain languages.
> However, the query takes forever to evaluate, even though I have a
> "limit 200" appended. Any ideas on how to optimize it?
>
> QUERY: ================================================
>
> SELECT distinct q.question_id
> FROM question_tags qt, questions q
> WHERE q.question_id = qt.question_id
> AND q.STATUS = 1
> AND not q.author_id = 105
> AND ((qt.language_id = 5 and qt.tag_id in
> (1101,917,734,550,367,183)) or (qt.language_id = 4 and qt.tag_id in
> (856,428)) or (qt.language_id =
> 3 and qt.tag_id in (1156,1028,899,771,642,514,385,257,128)) or
> (qt.language_id = 2 and qt.tag_id in
> (1193,1101,1009,917,826,734,642,550,458,367,275,183,91)))
> and q.question_id not in (413)
> LIMIT 200
>
> EXPLAIN ANALYZE: =========================================
>
> Limit (cost=1.50..1267.27 rows=200 width=4) (actual
> time=278.169..880.934 rows=200 loops=1)
> -> Unique (cost=1.50..317614.50 rows=50185 width=4) (actual
> time=278.165..880.843 rows=200 loops=1)
> -> Merge Join (cost=1.50..317489.04 rows=50185 width=4)
> (actual time=278.162..880.579 rows=441 loops=1)
> Merge Cond: (qt.question_id = q.question_id)
> -> Index Scan using question_tags_question_id on
> question_tags qt (cost=0.00..301256.96 rows=82051 width=4) (actual
> time=24.171..146.811 rows=6067 loops=1)
> Filter: (((language_id = 5) AND (tag_id = ANY
> ('{1101,917,734,550,367,183}'::integer[]))) OR ((language_id = 4) AND
> (tag_id = ANY ('{856,428}'::integer[]))) OR ((language_id = 3) AND
> (tag_id = ANY ('{1156,1028,899,771,642,514,385,257,128}'::integer[])))
> OR ((language_id = 2) AND (tag_id = ANY
> ('{1193,1101,1009,917,826,734,642,550,458,367,275,183,91}'::integer[]))))
> -> Index Scan using questions_pkey on questions q
> (cost=0.00..15464.12 rows=83488 width=4) (actual time=222.956..731.737
> rows=1000 loops=1)
> Filter: ((q.author_id <> 105) AND (q.question_id
> <> 413) AND (q.status = 1))
> Total runtime: 881.152 ms
> (9 rows)
An index on (language_id,tag_id) should be the first thing to try.
Or perhaps even (status,language_id,tag_id) or (language_id, tad_id,
status) (but that depends on the stats on "status" column).
An index on author_id will probably not be useful for this particular
query because your condition is "author_id != constant".
Also CLUSTER question_tags on (language_id, tad_id).
What is the database size versus RAM ? You must have a hell of a lot of
questions to make this slow... (or bloat ?)
^ permalink raw reply [nested|flat] 22+ messages in thread
* Query Optimization
@ 2010-04-08 12:58 Kaloyan Iliev Iliev <kaloyan@digsys.bg>
0 siblings, 2 replies; 22+ messages in thread
From: Kaloyan Iliev Iliev @ 2010-04-08 12:58 UTC (permalink / raw)
To: pgsql-performance
Hi,
Can anyone suggest why this query so slow.
SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 32-bit
(1 row)
explain analyze SELECT DT.value,
DT.meassure_date,
DT.ms_status_id as status_id,
S.descr_bg as status_bg,
S.descr_en as status_en,
VT.id as value_type_id,
VT.descr_en as value_type_en,
VT.descr_bg as value_type_bg,
T.unit as value_type_unit,
T.name as general_value_type,
T.ms_db_type_id
FROM
ms_data AS DT,
ms_statuses AS S,
ms_value_types AS VT,
ms_types AS T,
ms_commands_history AS CH
WHERE DT.ms_value_type_id = 88 AND
DT.meassure_date >= '2010-04-01 1:00:00' AND
DT.meassure_date <= '2010-04-01 1:10:00' AND
DT.ms_command_history_id = CH.id AND
CH.ms_device_id = 7 AND
DT.ms_value_type_id = VT.id AND
VT.ms_type_id = T.id AND
DT.ms_status_id = S.id
GROUP BY value,
meassure_date,
status_id,
status_bg,
status_en,
value_type_id,
value_type_en,
value_type_bg,
value_type_unit,
general_value_type,
ms_db_type_id
ORDER BY meassure_date DESC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=23.93..23.96 rows=1 width=229) (actual
time=63274.021..63274.021 rows=0
loops=1)
-> Sort (cost=23.93..23.94 rows=1 width=229) (actual
time=63274.016..63274.016 rows=0 loops=1)
Sort Key: dt.meassure_date, dt.value, dt.ms_status_id, s.descr_bg,
s.descr_en, vt.id, vt.descr_en, vt.descr_bg, t.unit, t.name,
t.ms_db_type_id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..23.92 rows=1 width=229) (actual
time=63273.982..63273.982 rows=0
loops=1)
-> Nested Loop (cost=0.00..19.64 rows=1 width=165) (actual
time=63273.977..63273.977 rows=0
loops=1)
-> Nested Loop (cost=0.00..15.36 rows=1 width=101)
(actual time=63273.974..63273.974 rows=0
loops=1)
-> Nested Loop (cost=0.00..11.08 rows=1 width=23)
(actual time=63273.970..63273.970 rows=0
loops=1)
-> Index Scan using
ms_commands_history_ms_device_id_idx on ms_commands_history ch
(cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 rows=9807
loops=1)
Index Cond: (ms_device_id = 7)
-> Index Scan using
ms_data_ms_command_history_id_idx on ms_data dt (cost=0.00..6.74 rows=1
width=31) (actual time=3.868..3.868 rows=0 loops=9807)
Index Cond:
(dt.ms_command_history_id = ch.id)
Filter: ((dt.meassure_date >=
'2010-04-01 01:00:00'::timestamp without time zone) AND
(dt.meassure_date <= '2010-04-01 01:10:00'::timestamp without time zone)
AND (dt.ms_value_type_id = 88))
-> Index Scan using ms_value_types_pkey on
ms_value_types vt (cost=0.00..4.27 rows=1 width=82) (never
executed) Index Cond:
(vt.id = 88)
-> Index Scan using ms_types_pkey on ms_types t
(cost=0.00..4.27 rows=1 width=72) (never
executed)
Index Cond: (t.id = vt.ms_type_id)
-> Index Scan using ms_statuses_pkey on ms_statuses s
(cost=0.00..4.27 rows=1 width=68) (never
executed) Index
Cond: (s.id = dt.ms_status_id)
Total runtime: 63274.256 ms
Thanks in advance.
Kaloyan Iliev
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query Optimization
@ 2010-04-08 13:41 Kaloyan Iliev Iliev <kaloyan@digsys.bg>
parent: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
1 sibling, 0 replies; 22+ messages in thread
From: Kaloyan Iliev Iliev @ 2010-04-08 13:41 UTC (permalink / raw)
To: pgsql-performance
Sorry for the noise.
I make vacuum analyze and the problem is solved.
Kaloyan Iliev
Kaloyan Iliev Iliev wrote:
> Hi,
> Can anyone suggest why this query so slow.
>
> SELECT version();
> version
> ---------------------------------------------------------------------------------------------------------
>
> PostgreSQL 8.4.2 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
> 4.2.1 20070719 [FreeBSD], 32-bit
> (1 row)
> explain analyze SELECT DT.value,
> DT.meassure_date,
> DT.ms_status_id as status_id,
> S.descr_bg as status_bg,
> S.descr_en as status_en,
> VT.id as value_type_id,
> VT.descr_en as value_type_en,
> VT.descr_bg as value_type_bg,
> T.unit as value_type_unit,
> T.name as general_value_type,
> T.ms_db_type_id
> FROM
> ms_data AS DT,
> ms_statuses AS S,
> ms_value_types AS VT,
> ms_types AS T,
> ms_commands_history AS CH
> WHERE DT.ms_value_type_id = 88 AND
> DT.meassure_date >= '2010-04-01 1:00:00' AND
> DT.meassure_date <= '2010-04-01 1:10:00' AND
> DT.ms_command_history_id = CH.id AND
> CH.ms_device_id = 7 AND
> DT.ms_value_type_id = VT.id AND
> VT.ms_type_id = T.id AND
> DT.ms_status_id = S.id
> GROUP BY value,
> meassure_date,
> status_id,
> status_bg,
> status_en,
> value_type_id,
> value_type_en,
> value_type_bg,
> value_type_unit,
> general_value_type,
> ms_db_type_id
> ORDER BY meassure_date DESC;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Group (cost=23.93..23.96 rows=1 width=229) (actual
> time=63274.021..63274.021 rows=0
> loops=1)
> -> Sort (cost=23.93..23.94 rows=1 width=229) (actual
> time=63274.016..63274.016 rows=0 loops=1)
> Sort Key: dt.meassure_date, dt.value, dt.ms_status_id, s.descr_bg,
> s.descr_en, vt.id, vt.descr_en, vt.descr_bg, t.unit, t.name,
> t.ms_db_type_id Sort Method: quicksort Memory: 17kB
> -> Nested Loop (cost=0.00..23.92 rows=1 width=229) (actual
> time=63273.982..63273.982 rows=0
> loops=1)
> -> Nested Loop (cost=0.00..19.64 rows=1 width=165)
> (actual time=63273.977..63273.977 rows=0
> loops=1)
> -> Nested Loop (cost=0.00..15.36 rows=1 width=101)
> (actual time=63273.974..63273.974 rows=0
> loops=1)
> -> Nested Loop (cost=0.00..11.08 rows=1 width=23)
> (actual time=63273.970..63273.970 rows=0
> loops=1)
> -> Index Scan using
> ms_commands_history_ms_device_id_idx on ms_commands_history ch
> (cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004
> rows=9807 loops=1)
> Index Cond: (ms_device_id = 7)
> -> Index Scan using
> ms_data_ms_command_history_id_idx on ms_data dt (cost=0.00..6.74
> rows=1 width=31) (actual time=3.868..3.868 rows=0 loops=9807)
> Index Cond:
> (dt.ms_command_history_id = ch.id)
> Filter: ((dt.meassure_date >=
> '2010-04-01 01:00:00'::timestamp without time zone) AND
> (dt.meassure_date <= '2010-04-01 01:10:00'::timestamp without time
> zone) AND (dt.ms_value_type_id = 88))
> -> Index Scan using ms_value_types_pkey on
> ms_value_types vt (cost=0.00..4.27 rows=1 width=82) (never
> executed) Index Cond:
> (vt.id = 88)
> -> Index Scan using ms_types_pkey on ms_types t
> (cost=0.00..4.27 rows=1 width=72) (never
> executed)
> Index Cond: (t.id = vt.ms_type_id)
> -> Index Scan using ms_statuses_pkey on ms_statuses s
> (cost=0.00..4.27 rows=1 width=68) (never
> executed) Index
> Cond: (s.id = dt.ms_status_id)
> Total runtime: 63274.256 ms
> Thanks in advance.
>
> Kaloyan Iliev
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Query Optimization
@ 2010-04-08 14:03 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
1 sibling, 0 replies; 22+ messages in thread
From: A. Kretschmer @ 2010-04-08 14:03 UTC (permalink / raw)
To: pgsql-performance
In response to Kaloyan Iliev Iliev :
> Hi,
> Can anyone suggest why this query so slow.
> -> Index Scan using
> ms_commands_history_ms_device_id_idx on ms_commands_history ch
> (cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 rows=9807
> loops=1)
Estimated rows: 1, actual rows: 9807, that's a BIG difference and,
maybe, your problem.
Btw.: your explain is hard to read (line-wrapping). It's better to
attach the explain as an own file...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
^ permalink raw reply [nested|flat] 22+ messages in thread
* query optimization
@ 2012-04-24 08:11 Richard Kojedzinszky <krichy@tvnetwork.hu>
0 siblings, 2 replies; 22+ messages in thread
From: Richard Kojedzinszky @ 2012-04-24 08:11 UTC (permalink / raw)
To: pgsql-performance
Dear list,
We have a database schema, which looks the same as the attached script.
When filling the tables with data, and skipping analyze on the table (so
pg_stats contains no records for table 'a'), the first select in the
script runs fast, but after an analyze the planner decides to sequence
scan tables b and c, thus making the query much slower. Can somebody help
me solving this issue, or tuning our installation to not to use sequence
scans in this case?
Thanks in advance,
Kojedzinszky Richard
Euronet Magyarorszag Informatikai Zrt.
Attachments:
test.sql (application/x-sql, 834B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-26 18:23 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Richard Kojedzinszky <krichy@tvnetwork.hu>
1 sibling, 0 replies; 22+ messages in thread
From: Kevin Grittner @ 2012-04-26 18:23 UTC (permalink / raw)
To: pgsql-performance; Richard Kojedzinszky <krichy@tvnetwork.hu>
Richard Kojedzinszky <krichy@tvnetwork.hu> wrote:
> tuning our installation to not to use sequence scans in this case?
Make sure effective_cache_size is set to the sum of shared_buffers
and whatever your OS shows as usable for caching. Try adjusting
cost factors: maybe random_page_cost between 1 and 2, and
cpu_tuple_cost between 0.03 and 0.05.
-Kevin
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-26 19:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Kojedzinszky <krichy@tvnetwork.hu>
1 sibling, 2 replies; 22+ messages in thread
From: Tom Lane @ 2012-04-26 19:17 UTC (permalink / raw)
To: Richard Kojedzinszky <krichy@tvnetwork.hu>; +Cc: pgsql-performance
Richard Kojedzinszky <krichy@tvnetwork.hu> writes:
> Dear list,
> We have a database schema, which looks the same as the attached script.
> When filling the tables with data, and skipping analyze on the table (so
> pg_stats contains no records for table 'a'), the first select in the
> script runs fast, but after an analyze the planner decides to sequence
> scan tables b and c, thus making the query much slower. Can somebody help
> me solving this issue, or tuning our installation to not to use sequence
> scans in this case?
Um ... did you analyze all the tables, or just some of them? I get
sub-millisecond runtimes if all four tables have been analyzed, but it
does seem to pick lousy plans if, say, only a and b have been analyzed.
What you really need for this query structure is the parameterized-path
work I've been doing for 9.2; but at least on the exact example given,
I'm not seeing that 9.1 is that much worse.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-26 19:38 Thomas Kellerer <spam_eater@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 22+ messages in thread
From: Thomas Kellerer @ 2012-04-26 19:38 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote on 26.04.2012 21:17:
> Richard Kojedzinszky<krichy@tvnetwork.hu> writes:
>> Dear list,
>> We have a database schema, which looks the same as the attached script.
>
>> When filling the tables with data, and skipping analyze on the table (so
>> pg_stats contains no records for table 'a'), the first select in the
>> script runs fast, but after an analyze the planner decides to sequence
>> scan tables b and c, thus making the query much slower. Can somebody help
>> me solving this issue, or tuning our installation to not to use sequence
>> scans in this case?
>
> Um ... did you analyze all the tables, or just some of them? I get
> sub-millisecond runtimes if all four tables have been analyzed, but it
> does seem to pick lousy plans if, say, only a and b have been analyzed.
>
Here it's similar to Richard's experience:
Before analyzing the four tables, the first statement yields this plan:
Merge Left Join (cost=504.89..2634509.91 rows=125000000 width=16) (actual time=0.103..0.108 rows=1 loops=1)
Merge Cond: (a.b = b.id)
-> Sort (cost=504.89..506.14 rows=500 width=8) (actual time=0.043..0.043 rows=1 loops=1)
Sort Key: a.b
Sort Method: quicksort Memory: 17kB
-> Bitmap Heap Scan on a (cost=12.14..482.47 rows=500 width=8) (actual time=0.028..0.029 rows=1 loops=1)
Recheck Cond: (id = 4)
-> Bitmap Index Scan on a_idx1 (cost=0.00..12.01 rows=500 width=0) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: (id = 4)
-> Materialize (cost=0.00..884002.52 rows=50000000 width=8) (actual time=0.041..0.057 rows=5 loops=1)
-> Merge Join (cost=0.00..759002.52 rows=50000000 width=8) (actual time=0.037..0.051 rows=5 loops=1)
Merge Cond: (b.id = c.id)
-> Index Scan using b_idx1 on b (cost=0.00..4376.26 rows=100000 width=4) (actual time=0.016..0.018 rows=5 loops=1)
-> Materialize (cost=0.00..4626.26 rows=100000 width=4) (actual time=0.017..0.022 rows=5 loops=1)
-> Index Scan using c_idx1 on c (cost=0.00..4376.26 rows=100000 width=4) (actual time=0.014..0.017 rows=5 loops=1)
Total runtime: 0.209 ms
This continues to stay the plan for about 10-15 repetitions, then it turns to this plan
Hash Right Join (cost=2701.29..6519.30 rows=1 width=16) (actual time=79.604..299.227 rows=1 loops=1)
Hash Cond: (b.id = a.b)
-> Hash Join (cost=2693.00..6136.00 rows=100000 width=8) (actual time=79.550..265.251 rows=100000 loops=1)
Hash Cond: (b.id = c.id)
-> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..36.158 rows=100000 loops=1)
-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual time=79.461..79.461 rows=100000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 2735kB
-> Seq Scan on c (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.010..32.930 rows=100000 loops=1)
-> Hash (cost=8.28..8.28 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Index Scan using a_idx1 on a (cost=0.00..8.28 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (id = 4)
Total runtime: 299.564 ms
(I guess autovacuum kicked in, because that the same plan I get when running analyze on all four tables right after populating them)
And the second one yields this one here (Regardless of analyze or not):
QUERY PLAN
Nested Loop Left Join (cost=0.00..16.89 rows=1 width=16) (actual time=0.027..0.031 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..16.57 rows=1 width=12) (actual time=0.020..0.022 rows=1 loops=1)
-> Index Scan using a_idx1 on a (cost=0.00..8.28 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (id = 4)
-> Index Scan using b_idx1 on b (cost=0.00..8.28 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (a.b = id)
-> Index Scan using c_idx1 on c (cost=0.00..0.31 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (b.id = id)
Total runtime: 0.104 ms
My version:
PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit
Running on Windows XP SP3
shared_buffers = 768MB
work_mem = 24MB
effective_cache_size = 1024MB
All other (relevant) settings are on defaults
Regards
Thomas
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-26 20:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2012-04-26 20:08 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-performance
Thomas Kellerer <spam_eater@gmx.net> writes:
> Tom Lane wrote on 26.04.2012 21:17:
>> Um ... did you analyze all the tables, or just some of them? I get
>> sub-millisecond runtimes if all four tables have been analyzed, but it
>> does seem to pick lousy plans if, say, only a and b have been analyzed.
> Here it's similar to Richard's experience:
> Before analyzing the four tables, the first statement yields this plan:
> [ merge joins ]
> This continues to stay the plan for about 10-15 repetitions, then it turns to this plan
> [ hash joins ]
Hmm. I see it liking the merge-join plan (with minor variations) with
or without analyze data, but if just some of the tables have been
analyzed, it goes for the hash plan which is a good deal slower. The
cost estimates aren't that far apart though. In any case, the only
reason the merge join is so fast is that the data is perfectly ordered
in each table; on a less contrived example, it could well be a lot
slower.
> And the second one yields this one here (Regardless of analyze or not):
Yeah, the trick there is that it's valid to re-order the joins, since
they're both left joins.
In git HEAD I get something like this:
regression=# explain analyze select * from a left join (b inner join c on b.id = c.id) on a.b = b.id where a.id = 4;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..17.18 rows=1 width=16) (actual time=0.024..0.026 rows=1 loops=1)
-> Index Scan using a_idx1 on a (cost=0.00..8.38 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = 4)
-> Nested Loop (cost=0.00..8.80 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
-> Index Only Scan using b_idx1 on b (cost=0.00..8.38 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (id = a.b)
Heap Fetches: 1
-> Index Only Scan using c_idx1 on c (cost=0.00..0.41 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (id = b.id)
Heap Fetches: 1
Total runtime: 0.080 ms
(11 rows)
but 9.1 and older are not smart enough to do it like that when they
can't re-order the joins.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-26 20:27 Andrew Dunstan <andrew@dunslane.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 22+ messages in thread
From: Andrew Dunstan @ 2012-04-26 20:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
On 04/26/2012 04:08 PM, Tom Lane wrote:
> Thomas Kellerer<spam_eater@gmx.net> writes:
>> Tom Lane wrote on 26.04.2012 21:17:
>>> Um ... did you analyze all the tables, or just some of them? I get
>>> sub-millisecond runtimes if all four tables have been analyzed, but it
>>> does seem to pick lousy plans if, say, only a and b have been analyzed.
>> Here it's similar to Richard's experience:
>> Before analyzing the four tables, the first statement yields this plan:
>> [ merge joins ]
>> This continues to stay the plan for about 10-15 repetitions, then it turns to this plan
>> [ hash joins ]
> Hmm. I see it liking the merge-join plan (with minor variations) with
> or without analyze data, but if just some of the tables have been
> analyzed, it goes for the hash plan which is a good deal slower. The
> cost estimates aren't that far apart though. In any case, the only
> reason the merge join is so fast is that the data is perfectly ordered
> in each table; on a less contrived example, it could well be a lot
> slower.
>
It's not so terribly contrived, is it? It's common enough to have tables
which are append-only and to join them by something that corresponds to
the append order (serial field, timestamp etc.)
cheers
andrew
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: query optimization
@ 2012-04-28 06:11 Richard Kojedzinszky <krichy@tvnetwork.hu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Kojedzinszky @ 2012-04-28 06:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Dear Tom,
Thanks for your response. I my test cases, until an analyze have been run,
the queries run fast. After only a have been analyzed, the query plan
changes so that a sequence scan for b and c tables is done, and joining
them with 'a' is done within memory.
So, my tests are here, with autovacuum turned off:
krichy=> \i test.sql
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
krichy=> explain analyze select * from a left join (b inner join c on b.id
= c.id) on a.b = b.id where a.id = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=504.88..2633669.90 rows=125000000 width=16)
(actual time=0.183..0.189 rows=1 loops=1)
Merge Cond: (a.b = b.id)
-> Sort (cost=504.88..506.13 rows=500 width=8) (actual
time=0.073..0.074 rows=1 loops=1)
Sort Key: a.b
Sort Method: quicksort Memory: 17kB
-> Bitmap Heap Scan on a (cost=12.13..482.47 rows=500 width=8)
(actual time=0.048..0.048 rows=1 loops=1)
Recheck Cond: (id = 1)
-> Bitmap Index Scan on a_idx1 (cost=0.00..12.01 rows=500
width=0) (actual time=0.044..0.044 rows=1 loops=1)
Index Cond: (id = 1)
-> Materialize (cost=0.00..883162.52 rows=50000000 width=8) (actual
time=0.103..0.108 rows=2 loops=1)
-> Merge Join (cost=0.00..758162.52 rows=50000000 width=8)
(actual time=0.100..0.104 rows=2 loops=1)
Merge Cond: (b.id = c.id)
-> Index Scan using b_idx1 on b (cost=0.00..3956.26
rows=100000 width=4) (actual time=0.050..0.050 rows=2 loops=1)
-> Materialize (cost=0.00..4206.26 rows=100000 width=4)
(actual time=0.048..0.049 rows=2 loops=1)
-> Index Scan using c_idx1 on c (cost=0.00..3956.26
rows=100000 width=4) (actual time=0.046..0.047 rows=2 loops=1)
Total runtime: 0.276 ms
(16 rows)
krichy=> ANALYZE a;
ANALYZE
krichy=> explain analyze select * from a left join (b inner join c on b.id
= c.id) on a.b = b.id where a.id = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=8.29..883178.31 rows=500 width=16) (actual
time=0.050..0.056 rows=1 loops=1)
Merge Cond: (b.id = a.b)
-> Merge Join (cost=0.00..758162.52 rows=50000000 width=8) (actual
time=0.034..0.038 rows=2 loops=1)
Merge Cond: (b.id = c.id)
-> Index Scan using b_idx1 on b (cost=0.00..3956.26 rows=100000
width=4) (actual time=0.015..0.016 rows=2 loops=1)
-> Materialize (cost=0.00..4206.26 rows=100000 width=4) (actual
time=0.015..0.017 rows=2 loops=1)
-> Index Scan using c_idx1 on c (cost=0.00..3956.26
rows=100000 width=4) (actual time=0.012..0.013 rows=2 loops=1)
-> Materialize (cost=8.29..8.29 rows=1 width=8) (actual
time=0.015..0.016 rows=1 loops=1)
-> Sort (cost=8.29..8.29 rows=1 width=8) (actual
time=0.013..0.013 rows=1 loops=1)
Sort Key: a.b
Sort Method: quicksort Memory: 17kB
-> Index Scan using a_idx1 on a (cost=0.00..8.28 rows=1
width=8) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 0.101 ms
(14 rows)
krichy=> ANALYZE b;
ANALYZE
krichy=> explain analyze select * from a left join (b inner join c on b.id
= c.id) on a.b = b.id where a.id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=2651.29..6419.30 rows=1 width=16) (actual
time=83.823..257.890 rows=1 loops=1)
Hash Cond: (b.id = a.b)
-> Hash Join (cost=2643.00..6036.00 rows=100000 width=8) (actual
time=83.790..224.552 rows=100000 loops=1)
Hash Cond: (c.id = b.id)
-> Seq Scan on c (cost=0.00..1393.00 rows=100000 width=4)
(actual time=0.010..35.925 rows=100000 loops=1)
-> Hash (cost=1393.00..1393.00 rows=100000 width=4) (actual
time=83.752..83.752 rows=100000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 2344kB
-> Seq Scan on b (cost=0.00..1393.00 rows=100000 width=4)
(actual time=0.009..36.302 rows=100000 loops=1)
-> Hash (cost=8.28..8.28 rows=1 width=8) (actual time=0.012..0.012
rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Index Scan using a_idx1 on a (cost=0.00..8.28 rows=1
width=8) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 258.245 ms
(13 rows)
krichy=> ANALYZE c;
ANALYZE
krichy=> explain analyze select * from a left join (b inner join c on b.id
= c.id) on a.b = b.id where a.id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=2651.29..6419.30 rows=1 width=16) (actual
time=83.295..255.653 rows=1 loops=1)
Hash Cond: (b.id = a.b)
-> Hash Join (cost=2643.00..6036.00 rows=100000 width=8) (actual
time=83.275..222.373 rows=100000 loops=1)
Hash Cond: (b.id = c.id)
-> Seq Scan on b (cost=0.00..1393.00 rows=100000 width=4)
(actual time=0.010..35.726 rows=100000 loops=1)
-> Hash (cost=1393.00..1393.00 rows=100000 width=4) (actual
time=83.238..83.238 rows=100000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 2344kB
-> Seq Scan on c (cost=0.00..1393.00 rows=100000 width=4)
(actual time=0.009..36.243 rows=100000 loops=1)
-> Hash (cost=8.28..8.28 rows=1 width=8) (actual time=0.011..0.011
rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Index Scan using a_idx1 on a (cost=0.00..8.28 rows=1
width=8) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 256.008 ms
(13 rows)
So after analyzing all the tables, the result changed much, psql uses
other plans to do the query, and in my case it is effectively much slower.
My configuration file has:
work_mem=16MB
with this removed, the query goes fast again, but I dont know why the more
memory makes psql chose a worse plan.
Thanks in advance,
Kojedzinszky Richard
Euronet Magyarorszag Informatikai Zrt.
On Thu, 26 Apr 2012, Tom Lane wrote:
> Date: Thu, 26 Apr 2012 15:17:18 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Richard Kojedzinszky <krichy@tvnetwork.hu>
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] query optimization
>
> Richard Kojedzinszky <krichy@tvnetwork.hu> writes:
>> Dear list,
>> We have a database schema, which looks the same as the attached script.
>
>> When filling the tables with data, and skipping analyze on the table (so
>> pg_stats contains no records for table 'a'), the first select in the
>> script runs fast, but after an analyze the planner decides to sequence
>> scan tables b and c, thus making the query much slower. Can somebody help
>> me solving this issue, or tuning our installation to not to use sequence
>> scans in this case?
>
> Um ... did you analyze all the tables, or just some of them? I get
> sub-millisecond runtimes if all four tables have been analyzed, but it
> does seem to pick lousy plans if, say, only a and b have been analyzed.
>
> What you really need for this query structure is the parameterized-path
> work I've been doing for 9.2; but at least on the exact example given,
> I'm not seeing that 9.1 is that much worse.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2012-04-28 06:11 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-14 21:34 Query Optimization sarlav kumar <sarlavk@yahoo.com>
2004-12-15 18:05 ` Andrew Lazarus <andrew@pillette.com>
2005-03-09 00:04 Query Optimization James G Wilkinson <jgw@alpinegeophysics.com>
2005-03-09 01:46 ` John A Meinel <john@arbash-meinel.com>
2007-02-19 17:03 Query Optimization Reinhard Vicinus <rvicinus@rjm.de>
2007-02-20 04:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-20 16:45 ` Jim C. Nasby <jim@nasby.net>
2007-07-31 01:08 Query optimization.... Karl Denninger <karl@denninger.net>
2008-03-26 18:36 Query Optimization Gopinath Narasimhan <gopinaras@yahoo.com>
2008-11-30 18:45 Query optimization tmp <skrald@amossen.dk>
2008-11-30 19:37 ` Re: Query optimization Marc Cousin <cousinmarc@gmail.com>
2008-12-01 13:24 ` Re: Query optimization PFC <lists@peufeu.com>
2010-04-08 12:58 Query Optimization Kaloyan Iliev Iliev <kaloyan@digsys.bg>
2010-04-08 13:41 ` Kaloyan Iliev Iliev <kaloyan@digsys.bg>
2010-04-08 14:03 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2012-04-24 08:11 query optimization Richard Kojedzinszky <krichy@tvnetwork.hu>
2012-04-26 18:23 ` Re: query optimization Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-26 19:17 ` Re: query optimization Tom Lane <tgl@sss.pgh.pa.us>
2012-04-26 19:38 ` Re: query optimization Thomas Kellerer <spam_eater@gmx.net>
2012-04-26 20:08 ` Re: query optimization Tom Lane <tgl@sss.pgh.pa.us>
2012-04-26 20:27 ` Re: query optimization Andrew Dunstan <andrew@dunslane.net>
2012-04-28 06:11 ` Re: query optimization Richard Kojedzinszky <krichy@tvnetwork.hu>
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