pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimization
48+ messages / 27 participants
[nested] [flat]
* Query optimization
@ 2002-10-04 08:17 Siva Kumar <tech@leatherlink.net>
0 siblings, 1 reply; 48+ messages in thread
From: Siva Kumar @ 2002-10-04 08:17 UTC (permalink / raw)
To: pgsql-general
We have a query as below:
=========================================================
select relationship_id as "Id",company_name as "Company",product_category_desc
as "Product",uom_desc as "UOM",shipment_term_desc as "Shipment
Term",payment_term_desc as "Payment Term",city_name as "Port",currency_name
as "Currency",activity_desc as "Activity",credit_days as "Credit Days" from
_100001relationships rs,master_member mm,member_product_details
mpd,master_product_category mpc,master_uom mu,master_shipment_term
mst,master_payment_term mpt,member_financial_details mfd,master_currency
mc,member_commercial_details mcd,master_activity ma,master_city mcy
where mpd.product_details_id = rs.product_details_id and
mpd.product_category_id=mpc.product_category_id and mpd.uom_id = mu.uom_id
and rs.shipment_term_id = mst.shipment_term_id and rs.payment_term_id =
mpt.payment_term_id and rs.commercial_details_id = mcd.commercial_details_id
and mcd.port = mcy.city_id and rs.financial_details_id =
mfd.financial_details_id and mfd.currency_id = mc.currency_id and
mpd.activity_id = ma.activity_id and mm.member_id = mpd.member_id
ORDER BY rs.relationship_id DESC;
=====================================================
The decision to keep the fields in different tables was taken in view of the
overall need of the system (there might be scope for improvement here too).
This query normally select about 10-20 rows. The problem is, the page load
takes about 4-5 seconds in the local network. The query run in psql terminal
takes about 2 second to execute (outputing 3 rows).
When hosted on the internet with most of our users using dialup connections,
and the query returning 10+ rows, this will not be acceptable.
Please point us towards the right direction to handle this type of problems.
Best regards,
Siva Kumar
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 10:14 Richard Huxton <dev@archonet.com>
parent: Siva Kumar <tech@leatherlink.net>
0 siblings, 2 replies; 48+ messages in thread
From: Richard Huxton @ 2002-10-04 10:14 UTC (permalink / raw)
To: Siva Kumar <tech@leatherlink.net>; pgsql-general
On Friday 04 Oct 2002 9:17 am, Siva Kumar wrote:
> We have a query as below:
[snip query with many joins]
>
> The decision to keep the fields in different tables was taken in view of
> the overall need of the system (there might be scope for improvement here
> too).
If that's the way the design makes sense, stick with it. It's better to get
Postgresql to handle a clean design rather than mangle a design.
> This query normally select about 10-20 rows. The problem is, the page
> load takes about 4-5 seconds in the local network. The query run in psql
> terminal takes about 2 second to execute (outputing 3 rows).
>
> When hosted on the internet with most of our users using dialup
> connections, and the query returning 10+ rows, this will not be acceptable.
Start by running EXPLAIN SELECT ... and looking at how the parser is handling
the query. One thing you might find useful is to use explicit JOINs to tell
Postgresql what order to connect the tables. You might prefere EXPLAIN
ANALYSE SELECT ... which will calculate actual times for each stage. See the
online manuals for details.
If you are missing indexes, you can add them. If Postgresql is not using
indexes you already have then we can look at why. Finally, if the plan looks
OK, we can look at tuning sort memory or similar.
First stage though, run an EXPLAIN and if you need help understanding it post
the output back to the list.
- Richard Huxton
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 12:10 Neil Conway <neilc@samurai.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 48+ messages in thread
From: Neil Conway @ 2002-10-04 12:10 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Siva Kumar <tech@leatherlink.net>; pgsql-general
Richard Huxton <dev@archonet.com> writes:
> First stage though, run an EXPLAIN and if you need help understanding it post
> the output back to the list.
Yep, that's a good idea. Another thing to try is to disable GEQO (SET
geqo = off;) and see what difference that makes to the performance of
the optimizer and the quality of the chosen plan (12 relations is
close to the cross-over point for the useability of the genetic
optimizer, IME).
Also, your comment regarding slow access over the Internet is not a
problem with PostgreSQL -- if it really *is* 2x or so slower when done
remotely, talk to your network admin.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 12:26 Siva Kumar <tech@leatherlink.net>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 3 replies; 48+ messages in thread
From: Siva Kumar @ 2002-10-04 12:26 UTC (permalink / raw)
To: pgsql-general
On Friday 04 Oct 2002 3:44 pm, you wrote:
> > The decision to keep the fields in different tables was taken in view of
> > the overall need of the system (there might be scope for improvement here
> > too).
>
> If that's the way the design makes sense, stick with it. It's better to get
> Postgresql to handle a clean design rather than mangle a design.
We had a relook at the design and managed to add one reduntant field in a
table which made sense otherwise also. This shaved some time from the earlier
query. now the query looks like this.
======================================
select relationship_id as "Id",company_name as
"Company",product_category_desc as "Product",uom_desc as
"UOM",shipment_term_desc as "Shipment Term",payment_term_desc as "Payment
Term",city_name as "Port",currency_name as "Currency",activity_desc as
"Activity",credit_days as "Credit Days"
from _100001relationships rs,master_member mm,member_product_details
mpd,master_product_category mpc,master_uom mu,master_shipment_term
mst,master_payment_term mpt,member_financial_details mfd,master_currency
mc,member_commercial_details mcd,master_activity ma,master_city mcy
where mpd.product_details_id = rs.product_details_id and
mpd.product_category_id=mpc.product_category_id and mpd.uom_id = mu.uom_id
and rs.shipment_term_id = mst.shipment_term_id and rs.payment_term_id =
mpt.payment_term_id and rs.commercial_details_id = mcd.commercial_details_id
and mcd.port = mcy.city_id and rs.financial_details_id =
mfd.financial_details_id and mfd.currency_id = mc.currency_id and
mpd.activity_id = ma.activity_id and mm.member_id = rs.partner_id ORDER BY
rs.relationship_id DESC;
==================================================
> First stage though, run an EXPLAIN and if you need help understanding it
> post the output back to the list.
Giving below the output of EXPLAIN ANALYSE. I could not make much sense out of
it, please help!
NOTICE: QUERY PLAN:
Sort (cost=1382.45..1382.45 rows=1000 width=442) (actual time=3.47..3.47
rows=3 loops=1)
-> Merge Join (cost=1263.12..1332.62 rows=1000 width=442) (actual
time=3.21..3.27 rows=3 loops=1)
-> Index Scan using master_activity_pkey on master_activity ma
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.06..0.09 rows=4 loops=1)
-> Sort (cost=1263.12..1263.12 rows=1000 width=392) (actual
time=3.04..3.05 rows=3 loops=1)
-> Merge Join (cost=1143.79..1213.29 rows=1000 width=392)
(actual time=2.87..2.95 rows=3 loops=1)
-> Index Scan using master_member_pkey on master_member
mm (cost=0.00..52.00 rows=1000 width=47) (actual time=0.03..0.23 rows=45
loops=1)
-> Sort (cost=1143.79..1143.79 rows=1000 width=345)
(actual time=2.48..2.48 rows=3 loops=1)
-> Merge Join (cost=1024.46..1093.96 rows=1000
width=345) (actual time=2.30..2.41 rows=3 loops=1)
-> Index Scan using
master_product_category_pkey on master_product_category mpc
(cost=0.00..52.00 rows=1000 width=37) (actual time=0.02..0.08 rows=7 loops=1)
-> Sort (cost=1024.46..1024.46 rows=1000
width=308) (actual time=2.17..2.17 rows=3 loops=1)
-> Merge Join (cost=905.13..974.63
rows=1000 width=308) (actual time=1.96..2.10 rows=3 loops=1)
-> Index Scan using
master_uom_pkey on master_uom mu (cost=0.00..52.00 rows=1000 width=41)
(actual time=0.03..0.06 rows=6 loops=1)
-> Sort (cost=905.13..905.13
rows=1000 width=267) (actual time=1.87..1.87 rows=3 loops=1)
-> Merge Join
(cost=785.80..855.30 rows=1000 width=267) (actual time=1.70..1.77 rows=3
loops=1)
-> Index Scan using
member_product_details_pkey on member_product_details mpd (cost=0.00..52.00
rows=1000 width=23) (actual time=0.03..0.11 rows=14 loops=1)
-> Sort
(cost=785.80..785.80 rows=1000 width=244) (actual time=1.53..1.53 rows=3
loops=1)
-> Merge Join
(cost=666.47..735.97 rows=1000 width=244) (actual time=1.35..1.47 rows=3
loops=1)
-> Index
Scan using master_shipment_term_pkey on master_shipment_term mst
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.06 rows=5 loops=1)
-> Sort
(cost=666.47..666.47 rows=1000 width=194) (actual time=1.27..1.27 rows=3
loops=1)
->
Merge Join (cost=547.14..616.64 rows=1000 width=194) (actual time=1.12..1.19
rows=3 loops=1)
-> Index Scan using master_city_pkey on master_city mcy (cost=0.00..52.00
rows=1000 width=47) (actual time=0.02..0.04 rows=4 loops=1)
-> Sort (cost=547.14..547.14 rows=1000 width=147) (actual time=1.05..1.06
rows=3 loops=1)
-> Merge Join (cost=427.82..497.32 rows=1000 width=147) (actual
time=0.92..1.00 rows=3 loops=1)
-> Index Scan using master_currency_pkey on master_currency mc
(cost=0.00..52.00 rows=1000 width=40) (actual time=0.03..0.06 rows=4 loops=1)
-> Sort (cost=427.82..427.82 rows=1000 width=107) (actual time=0.81..0.81
rows=3 loops=1)
-> Merge Join (cost=308.49..377.99 rows=1000 width=107) (actual
time=0.69..0.74 rows=3 loops=1)
-> Index Scan using member_financial_details_pkey on
member_financial_details mfd (cost=0.00..52.00 rows=1000 width=11) (actual
time=0.03..0.08 rows=11 loops=1)
-> Sort (cost=308.49..308.49 rows=1000 width=96) (actual time=0.56..0.57
rows=3 loops=1)
-> Merge Join (cost=189.16..258.66 rows=1000 width=96) (actual
time=0.46..0.51 rows=3 loops=1)
-> Index Scan using master_payment_term_pkey on master_payment_term mpt
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.04 rows=4 loops=1)
-> Sort (cost=189.16..189.16 rows=1000 width=46) (actual time=0.35..0.35
rows=3 loops=1)
-> Merge Join (cost=69.83..139.33 rows=1000 width=46) (actual
time=0.21..0.25 rows=3 loops=1)
-> Index Scan using member_commercial_details_pkey on
member_commercial_details mcd (cost=0.00..52.00 rows=1000 width=8) (actual
time=0.02..0.06 rows=6 loops=1)
-> Sort (cost=69.83..69.83 rows=1000 width=38) (actual time=0.11..0.11
rows=3 loops=1)
-> Seq Scan on _100001relationships rs (cost=0.00..20.00 rows=1000
width=38) (actual time=0.03..0.05 rows=3 loops=1)
Total runtime: 5.45 msec
=====================================================
Best regards,
Siva Kumar
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 12:42 Jochem van Dieten <jochemd@oli.tudelft.nl>
parent: Siva Kumar <tech@leatherlink.net>
2 siblings, 0 replies; 48+ messages in thread
From: Jochem van Dieten @ 2002-10-04 12:42 UTC (permalink / raw)
To: Siva Kumar <tech@leatherlink.net>; +Cc: pgsql-general
Quoting Siva Kumar <tech@leatherlink.net>:
>
>
> Giving below the output of EXPLAIN ANALYSE. I could not make much
> sense out of
> it, please help!
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=1382.45..1382.45 rows=1000 width=442) (actual
> time=3.47..3.47
> rows=3 loops=1)
> -> Merge Join (cost=1263.12..1332.62 rows=1000 width=442) (actual
>
> time=3.21..3.27 rows=3 loops=1)
> -> Index Scan using master_activity_pkey on master_activity
> ma
> (cost=0.00..52.00 rows=1000 width=50) (actual time=0.06..0.09 rows=4
> loops=1)
EXPLAIN returns the way that PostgreSQL will try to get data from the
different tables and indexes and combine that together to produce the
query result. To choose the best way, PostgreSQL is dependent on
statistical information on the content of the tables. Those statistics
are not collected automatically, you need to tell PostgreSQL to collect
these statistics.
Your EXPLAIN plan always returns an estimate of 1000 rows for each
operation. This is usually an indication that you didn't gather
statistics yet. You need to run ANALYZE first and then try again.
Also, take a look at the manual entry for VACUUM, which is another
maintenance operation that can be critical for performance.
If your database is not in production, I would recommend you do a
VACUUM FULL ANALYZE and then try the EXPLAIN again. If your database is
in production, do VACUUM ANALYZE and read up on the FULL part so you
know when to use that.
After that, see if performance got better and post the new EXPLAIN
output.
Jochem
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 12:42 Antonis Antoniou <a.antoniou@albourne.com>
parent: Siva Kumar <tech@leatherlink.net>
2 siblings, 0 replies; 48+ messages in thread
From: Antonis Antoniou @ 2002-10-04 12:42 UTC (permalink / raw)
To: Siva Kumar <tech@leatherlink.net>; +Cc: pgsql-general
Siva Kumar wrote:
>On Friday 04 Oct 2002 3:44 pm, you wrote:
>
>
>>>The decision to keep the fields in different tables was taken in view of
>>>the overall need of the system (there might be scope for improvement here
>>>too).
>>>
>>>
>>If that's the way the design makes sense, stick with it. It's better to get
>>Postgresql to handle a clean design rather than mangle a design.
>>
>>
>
>We had a relook at the design and managed to add one reduntant field in a
>table which made sense otherwise also. This shaved some time from the earlier
>query. now the query looks like this.
>======================================
> select relationship_id as "Id",company_name as
>"Company",product_category_desc as "Product",uom_desc as
>"UOM",shipment_term_desc as "Shipment Term",payment_term_desc as "Payment
>Term",city_name as "Port",currency_name as "Currency",activity_desc as
>"Activity",credit_days as "Credit Days"
>
>from _100001relationships rs,master_member mm,member_product_details
>mpd,master_product_category mpc,master_uom mu,master_shipment_term
>mst,master_payment_term mpt,member_financial_details mfd,master_currency
>mc,member_commercial_details mcd,master_activity ma,master_city mcy
>
> where mpd.product_details_id = rs.product_details_id and
>mpd.product_category_id=mpc.product_category_id and mpd.uom_id = mu.uom_id
>and rs.shipment_term_id = mst.shipment_term_id and rs.payment_term_id =
>mpt.payment_term_id and rs.commercial_details_id = mcd.commercial_details_id
>and mcd.port = mcy.city_id and rs.financial_details_id =
>mfd.financial_details_id and mfd.currency_id = mc.currency_id and
>mpd.activity_id = ma.activity_id and mm.member_id = rs.partner_id ORDER BY
>rs.relationship_id DESC;
>==================================================
>
>
>
>>First stage though, run an EXPLAIN and if you need help understanding it
>>post the output back to the list.
>>
>>
>
>Giving below the output of EXPLAIN ANALYSE. I could not make much sense out of
>it, please help!
>
>NOTICE: QUERY PLAN:
>
>Sort (cost=1382.45..1382.45 rows=1000 width=442) (actual time=3.47..3.47
>rows=3 loops=1)
> -> Merge Join (cost=1263.12..1332.62 rows=1000 width=442) (actual
>time=3.21..3.27 rows=3 loops=1)
> -> Index Scan using master_activity_pkey on master_activity ma
>(cost=0.00..52.00 rows=1000 width=50) (actual time=0.06..0.09 rows=4 loops=1)
> -> Sort (cost=1263.12..1263.12 rows=1000 width=392) (actual
>time=3.04..3.05 rows=3 loops=1)
> -> Merge Join (cost=1143.79..1213.29 rows=1000 width=392)
>(actual time=2.87..2.95 rows=3 loops=1)
> -> Index Scan using master_member_pkey on master_member
>mm (cost=0.00..52.00 rows=1000 width=47) (actual time=0.03..0.23 rows=45
>loops=1)
> -> Sort (cost=1143.79..1143.79 rows=1000 width=345)
>(actual time=2.48..2.48 rows=3 loops=1)
> -> Merge Join (cost=1024.46..1093.96 rows=1000
>width=345) (actual time=2.30..2.41 rows=3 loops=1)
> -> Index Scan using
>master_product_category_pkey on master_product_category mpc
>(cost=0.00..52.00 rows=1000 width=37) (actual time=0.02..0.08 rows=7 loops=1)
> -> Sort (cost=1024.46..1024.46 rows=1000
>width=308) (actual time=2.17..2.17 rows=3 loops=1)
> -> Merge Join (cost=905.13..974.63
>rows=1000 width=308) (actual time=1.96..2.10 rows=3 loops=1)
> -> Index Scan using
>master_uom_pkey on master_uom mu (cost=0.00..52.00 rows=1000 width=41)
>(actual time=0.03..0.06 rows=6 loops=1)
> -> Sort (cost=905.13..905.13
>rows=1000 width=267) (actual time=1.87..1.87 rows=3 loops=1)
> -> Merge Join
>(cost=785.80..855.30 rows=1000 width=267) (actual time=1.70..1.77 rows=3
>loops=1)
> -> Index Scan using
>member_product_details_pkey on member_product_details mpd (cost=0.00..52.00
>rows=1000 width=23) (actual time=0.03..0.11 rows=14 loops=1)
> -> Sort
>(cost=785.80..785.80 rows=1000 width=244) (actual time=1.53..1.53 rows=3
>loops=1)
> -> Merge Join
>(cost=666.47..735.97 rows=1000 width=244) (actual time=1.35..1.47 rows=3
>loops=1)
> -> Index
>Scan using master_shipment_term_pkey on master_shipment_term mst
>(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.06 rows=5 loops=1)
> -> Sort
>(cost=666.47..666.47 rows=1000 width=194) (actual time=1.27..1.27 rows=3
>loops=1)
> ->
>Merge Join (cost=547.14..616.64 rows=1000 width=194) (actual time=1.12..1.19
>rows=3 loops=1)
>
>-> Index Scan using master_city_pkey on master_city mcy (cost=0.00..52.00
>rows=1000 width=47) (actual time=0.02..0.04 rows=4 loops=1)
>
>-> Sort (cost=547.14..547.14 rows=1000 width=147) (actual time=1.05..1.06
>rows=3 loops=1)
>
>-> Merge Join (cost=427.82..497.32 rows=1000 width=147) (actual
>time=0.92..1.00 rows=3 loops=1)
>
>-> Index Scan using master_currency_pkey on master_currency mc
>(cost=0.00..52.00 rows=1000 width=40) (actual time=0.03..0.06 rows=4 loops=1)
>
>-> Sort (cost=427.82..427.82 rows=1000 width=107) (actual time=0.81..0.81
>rows=3 loops=1)
>
>-> Merge Join (cost=308.49..377.99 rows=1000 width=107) (actual
>time=0.69..0.74 rows=3 loops=1)
>
>-> Index Scan using member_financial_details_pkey on
>member_financial_details mfd (cost=0.00..52.00 rows=1000 width=11) (actual
>time=0.03..0.08 rows=11 loops=1)
>
>-> Sort (cost=308.49..308.49 rows=1000 width=96) (actual time=0.56..0.57
>rows=3 loops=1)
>
>-> Merge Join (cost=189.16..258.66 rows=1000 width=96) (actual
>time=0.46..0.51 rows=3 loops=1)
>
>-> Index Scan using master_payment_term_pkey on master_payment_term mpt
>(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.04 rows=4 loops=1)
>
>-> Sort (cost=189.16..189.16 rows=1000 width=46) (actual time=0.35..0.35
>rows=3 loops=1)
>
>-> Merge Join (cost=69.83..139.33 rows=1000 width=46) (actual
>time=0.21..0.25 rows=3 loops=1)
>
>-> Index Scan using member_commercial_details_pkey on
>member_commercial_details mcd (cost=0.00..52.00 rows=1000 width=8) (actual
>time=0.02..0.06 rows=6 loops=1)
>
>-> Sort (cost=69.83..69.83 rows=1000 width=38) (actual time=0.11..0.11
>rows=3 loops=1)
>
>-> Seq Scan on _100001relationships rs (cost=0.00..20.00 rows=1000
>width=38) (actual time=0.03..0.05 rows=3 loops=1)
>Total runtime: 5.45 msec
>
>=====================================================
>
>Best regards,
>
>
>Siva Kumar
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
http://www.postgresql.org/idocs/index.php?performance-tips.html
First, read this documentation.
Thanks
Antonis
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-04 13:29 Richard Huxton <dev@archonet.com>
parent: Siva Kumar <tech@leatherlink.net>
2 siblings, 1 reply; 48+ messages in thread
From: Richard Huxton @ 2002-10-04 13:29 UTC (permalink / raw)
To: Siva Kumar <tech@leatherlink.net>; pgsql-general
On Friday 04 Oct 2002 1:26 pm, Siva Kumar wrote:
> Giving below the output of EXPLAIN ANALYSE. I could not make much sense out
> of it, please help!
Scary aren't they ;-)
The important thing is you've got plenty of "Index Scan"s rather than Seq
Scans. The other point is the final time:
> Total runtime: 5.45 msec
Now, since that's not what you're getting, I'd think Neil (see other reply) is
right and you need to look at issuing "SET geqo = off;" before the query.
Postgresql has a genetic algorithm that kicks in on what it thinks is a very
complex query, this can take a long time to analyse the options available but
pays dividends on a big query. In your case you're only getting a few rows
and so it takes longer to analyse than to get the results.
The other thing that might work is rewriting the query with explicit JOINs - I
think that should make it clear to Postgresql what order to do things in.
- Richard Huxton
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2002-10-07 11:28 Siva Kumar <tech@leatherlink.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 48+ messages in thread
From: Siva Kumar @ 2002-10-07 11:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-general
Thanks to Richard, Neil, Jochem and Antonis.
I went through the documentation on explain/analyse. Finally, decided to split
up the query into two and to reduce the number of tables to get faster query.
In short, I have not solved the problem as presented (too lazy of me :-( ,
but opted to walk an easier path.
Thanks once again.
Best regards,
Siva Kumar
On Friday 04 Oct 2002 6:59 pm, Richard Huxton wrote:
> On Friday 04 Oct 2002 1:26 pm, Siva Kumar wrote:
> > Giving below the output of EXPLAIN ANALYSE. I could not make much sense
> > out of it, please help!
>
> Scary aren't they ;-)
>
> The important thing is you've got plenty of "Index Scan"s rather than Seq
>
> Scans. The other point is the final time:
> > Total runtime: 5.45 msec
>
> Now, since that's not what you're getting, I'd think Neil (see other reply)
> is right and you need to look at issuing "SET geqo = off;" before the
> query.
>
> Postgresql has a genetic algorithm that kicks in on what it thinks is a
> very complex query, this can take a long time to analyse the options
> available but pays dividends on a big query. In your case you're only
> getting a few rows and so it takes longer to analyse than to get the
> results.
>
> The other thing that might work is rewriting the query with explicit JOINs
> - I think that should make it clear to Postgresql what order to do things
> in.
>
> - Richard Huxton
^ permalink raw reply [nested|flat] 48+ messages in thread
* query optimization
@ 2002-10-15 07:06 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 2 replies; 48+ messages in thread
From: pginfo @ 2002-10-15 07:06 UTC (permalink / raw)
To: pgsql-general
Hi,
By running a relative complex query I get very slow response from
postgresql.
The query is and the explain analyze are:
testsklad19=# explain analyze select
N.IDS,N.IDS_NUM,N.IDS_SLU,N.IDS_SKLAD,SK.MNAME AS
SKNAME,N.ZAPR,N.NOMNUM,NOM.NUM,N.NOMNAME,NOM.MNAME AS
NOMNAME,N.PART,N.SERIENNUM,N.IZV,N.KOL,N.IDS_MED,N.MED,N.IDS_MED_MAIN,N.MED_MAIN,N.OTN_MED,N.CENA,N.VAL,N.KURS,N.CENADDS,N.DDS,N.CENA_LV,N.CENA_LVDDS,N.TOT,N.DTO,N.PTO,N.ORDER_NUM,N.IDS_DOC2,NOM.OSN_MED,NOM.DOP1_MED,NOM.DOP2_MED,NOM.OTN_DOP1_MED,NOM.OTN_DOP2_MED,N.DTON
,N.OFFIC,N.DATE_IZL,N.IZL,N.OTCH_CENA,N.CENAMITALV,N.CENATAKSILV,N.CENATRANSLV,N.CENA,N.CENAZASTRLV
from A_SKLAD N left outer join A_LOCATION SK ON ( N.IDS_SKLAD=SK.IDS )
left outer join A_NOMEN NOM ON ( N.IDS_NUM = NOM.IDS ) where N.FID = 0
AND N.IDS_DOC = 'SOF_500' ORDER BY N.ORDER_NUM ;
NOTICE: QUERY PLAN:
Sort (cost=37.44..37.44 rows=1 width=1118) (actual
time=12140.96..12141.02 rows=48 loops=1)
-> Nested Loop (cost=0.00..37.43 rows=1 width=1118) (actual
time=115.02..12138.57 rows=48 loops=1)
-> Nested Loop (cost=0.00..33.42 rows=1 width=886) (actual
time=0.58..20.77 rows=48 loops=1)
-> Index Scan using i_sklad_ids_doc on a_sklad n
(cost=0.00..28.58 rows=1 width=760) (actual time=0.36..8.59 rows=48
loops=1)
-> Index Scan using a_location_pkey on a_location sk
(cost=0.00..4.82 rows=1 width=126) (actual time=0.09..0.12 rows=1
loops=48)
-> Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232)
(actual time=0.03..165.45 rows=6702 loops=48)
Total runtime: 12142.07 msec
EXPLAIN
How can I detect the problem?
Exist any info or docs about how to interpred the analyze results?
Many thanks in advance,
Ivan.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2002-10-15 08:12 Mario Weilguni <mweilguni@sime.com>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 0 replies; 48+ messages in thread
From: Mario Weilguni @ 2002-10-15 08:12 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-general
> Sort (cost=37.44..37.44 rows=1 width=1118) (actual
> time=12140.96..12141.02 rows=48 loops=1)
> -> Nested Loop (cost=0.00..37.43 rows=1 width=1118) (actual
> time=115.02..12138.57 rows=48 loops=1)
> -> Nested Loop (cost=0.00..33.42 rows=1 width=886) (actual
> time=0.58..20.77 rows=48 loops=1)
> -> Index Scan using i_sklad_ids_doc on a_sklad n
> (cost=0.00..28.58 rows=1 width=760) (actual time=0.36..8.59 rows=48
> loops=1)
> -> Index Scan using a_location_pkey on a_location sk
> (cost=0.00..4.82 rows=1 width=126) (actual time=0.09..0.12 rows=1
> loops=48)
> -> Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232)
> (actual time=0.03..165.45 rows=6702 loops=48)
> Total runtime: 12142.07 msec
It seems the query planner is completly wrong here, look on the line
Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232) (actual time=0.03..165.45 rows=6702 loops=48)
This means the planner expects 45 return rows (guessed from statistics), but actually gets 6702 rows.
Do "VACUUM ANALYZE a_nomen" and try your query again.
If it fails:
Do you have a unique index on a_nomen(ids)?
Regards,
Mario Weilguni
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2002-10-15 13:03 Gaetano Mendola <mendola@bigfoot.com>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 0 replies; 48+ messages in thread
From: Gaetano Mendola @ 2002-10-15 13:03 UTC (permalink / raw)
To: pgsql-general
>
> The query is and the explain analyze are:
>
> testsklad19=# explain analyze select
> N.IDS,N.IDS_NUM,N.IDS_SLU,N.IDS_SKLAD,SK.MNAME AS
> SKNAME,N.ZAPR,N.NOMNUM,NOM.NUM,N.NOMNAME,NOM.MNAME AS
>
NOMNAME,N.PART,N.SERIENNUM,N.IZV,N.KOL,N.IDS_MED,N.MED,N.IDS_MED_MAIN,N.MED_
MAIN,N.OTN_MED,N.CENA,N.VAL,N.KURS,N.CENADDS,N.DDS,N.CENA_LV,N.CENA_LVDDS,N.
TOT,N.DTO,N.PTO,N.ORDER_NUM,N.IDS_DOC2,NOM.OSN_MED,NOM.DOP1_MED,NOM.DOP2_MED
,NOM.OTN_DOP1_MED,NOM.OTN_DOP2_MED,N.DTON
>
,N.OFFIC,N.DATE_IZL,N.IZL,N.OTCH_CENA,N.CENAMITALV,N.CENATAKSILV,N.CENATRANS
LV,N.CENA,N.CENAZASTRLV
> from A_SKLAD N left outer join A_LOCATION SK ON ( N.IDS_SKLAD=SK.IDS )
> left outer join A_NOMEN NOM ON ( N.IDS_NUM = NOM.IDS ) where N.FID = 0
> AND N.IDS_DOC = 'SOF_500' ORDER BY N.ORDER_NUM ;
> NOTICE: QUERY PLAN:
>
Well,
can you tell us the index that are present on table A_SKLAD ?
It seem that there are no index on the field FID, BTW if for you
are always important only the line whit FID = 0 ( or other few values )
you can create a partial index:
CREATE INDEX idx_partial ON a_sklad ( fid ) WHERE FID = 0;
or if the values are more:
CREATE INDEX idx_partial ON a_sklad ( fid ) WHERE FID IN ( val1, val2,
.... );
Dont forget to do: 'vacuum analyze a_skland' after index creation.
Good luck
Gaetano
^ permalink raw reply [nested|flat] 48+ messages in thread
* query optimization
@ 2005-08-12 16:11 marcelo Cortez <jmdc_marcelo@yahoo.com.ar>
0 siblings, 1 reply; 48+ messages in thread
From: marcelo Cortez @ 2005-08-12 16:11 UTC (permalink / raw)
To: pgsql-general
hi guys
i need advice for query optimization,take too long
time.
any coments are welcomed :).
best
MDC
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_reparticion)
INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext =
r2.id_reparticion)
INNER JOIN repartit AS r3 ON (c.id_reparticion_car =
r3.id_reparticion)
LEFT JOIN carintet AS i ON (c.actuacion_car =
i.actuacion_int)
LEFT JOIN repartit AS r5 ON (i.id_reparticion_i =
r5.id_reparticion)
LEFT JOIN repartit AS r6 ON (i.id_reparticion_s =
r6.id_reparticion)
LEFT JOIN carcallt AS l ON (c.actuacion_car =
l.actuacion_cal)
LEFT JOIN callest AS ll9 ON (l.id_calle_cal =
ll9.id_calle)
LEFT JOIN callest AS ll10 ON (l.id_calle1_cal =
ll10.id_calle)
LEFT JOIN callest AS ll11 ON (l.id_calle2_cal =
ll11.id_calle)
LEFT JOIN callest AS ll12 ON (l.id_esquina_cal =
ll12.id_calle)
LEFT JOIN pasest AS p ON (c.actuacion_car =
p.act_principal)
LEFT JOIN repartit AS r7 ON (p.id_repart_origen =
r7.id_reparticion)
LEFT JOIN repartit AS r8 ON (p.id_repart_destino =
r8.id_reparticion)
LEFT JOIN repartit AS r9 ON (p.id_reparticion_u =
r9.id_reparticion)
WHERE letra(i.nota_iniciadora) = 'NO' AND
anio(i.nota_iniciadora) = '2005' AND
numero(i.nota_iniciadora) = '12' AND
repart(i.nota_iniciadora) = 'DGRH'
LIMIT 101
TABLE DEFINITION
CREATE TABLE caratult
(
actuacion_car char(24) NOT NULL,
id_reparticion_uc int4 NOT NULL,
fecha_inicio timestamp NOT NULL,
tipo_actuacion char(1) NOT NULL,
id_extracto_car int4,
act_extramunicipal char(35),
observaciones varchar(250),
comentario1 varchar(250) NOT NULL,
comentario2 varchar(250),
comentario3 varchar(250),
si_calle char(1) NOT NULL,
verdadera char(1) NOT NULL,
orden_pago char(10),
fac_tipo char(2),
fac_anio numeric(4),
fac_nro numeric(8),
fac_importe numeric(13),
anexos varchar(50),
recibo_suelto char(1) NOT NULL,
id_actuacion_car int4 NOT NULL,
id_reparticion_car int4 NOT NULL,
id_secuencia_car int4 NOT NULL,
fecha_inicio_real date NOT NULL,
fts_comentario tsvector,
fts_observaciones tsvector,
CONSTRAINT pk_caratult PRIMARY KEY (actuacion_car),
CONSTRAINT fx_actuacit FOREIGN KEY
(id_actuacion_car)
REFERENCES actuacit (id_actuacion) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_extracto FOREIGN KEY (id_extracto_car)
REFERENCES extractt (id_extracto) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_car FOREIGN KEY
(id_reparticion_car)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_uc FOREIGN KEY
(id_reparticion_uc)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_secuencia1 FOREIGN KEY
(id_secuencia_car)
REFERENCES secuenct (id_secuencia) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE caratult OWNER TO postgres;
GRANT ALL ON TABLE caratult TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE caratult TO GROUP devel;
QUERY PLAN RESULT
Limit (cost=0.00..31798.82 rows=4 width=457)
-> Nested Loop Left Join (cost=0.00..31798.82
rows=4 width=457)
-> Nested Loop Left Join
(cost=0.00..31774.69 rows=4 width=461)
-> Nested Loop Left Join
(cost=0.00..31750.61 rows=4 width=465)
-> Nested Loop Left Join
(cost=0.00..31726.53 rows=4 width=469)
-> Nested Loop Left Join
(cost=0.00..31558.52 rows=1 width=457)
-> Nested Loop Left
Join (cost=0.00..31552.53 rows=1 width=461)
-> Nested Loop
Left Join (cost=0.00..31546.54 rows=1 width=465)
-> Nested
Loop Left Join (cost=0.00..31540.55 rows=1 width=469)
->
Nested Loop Left Join (cost=0.00..31534.56 rows=1
width=473)
-> Nested Loop Left Join (cost=0.00..31528.53
rows=1 width=457)
-> Nested Loop Left Join (cost=0.00..31522.51
rows=1 width=461)
-> Nested Loop (cost=0.00..31516.49
rows=1 width=465)
-> Nested Loop
(cost=0.00..31510.47 rows=1 width=469)
-> Nested Loop
(cost=0.00..31504.64 rows=1 width=469)
-> Nested Loop
(cost=0.00..31498.62 rows=1 width=473)
-> Nested Loop
(cost=0.00..31492.60 rows=1 width=477)
-> Seq
Scan on carintet i (cost=0.00..31486.57 rows=1
width=36)
Filter: ((substr((nota_iniciadora)::text, 1, 2) =
'NO'::text) AND (substr((nota_iniciadora)::text, 3, 4)
= '2005'::text) AND
("replace"(substr((nota_iniciadora)::text, 7, 6), '
'::text, ''::text) = '12'::text) AND
(substr((nota_iniciadora)::text, 16, 9) =
'DGRH'::text))
-> Index
Scan using ix1_caratult on caratult c
(cost=0.00..6.01 rows=1 width=469)
Index
Cond: (c.actuacion_car = "outer".actuacion_int)
-> Index Scan
using pk_repartit on repartit r1 (cost=0.00..6.01
rows=1 width=4)
Index Cond:
("outer".id_reparticion_uc = r1.id_reparticion)
-> Index Scan using
pk_repartit on repartit r3 (cost=0.00..6.01 rows=1
width=4)
Index Cond:
("outer".id_reparticion_car = r3.id_reparticion)
-> Index Scan using
pk_extractt on extractt t1 (cost=0.00..5.82 rows=1
width=8)
Index Cond:
("outer".id_extracto_car = t1.id_extracto)
-> Index Scan using pk_repartit on
repartit r2 (cost=0.00..6.01 rows=1 width=4)
Index Cond:
("outer".id_reparticion_ext = r2.id_reparticion)
-> Index Scan using pk_repartit on
repartit r5 (cost=0.00..6.01 rows=1 width=4)
Index Cond:
("outer".id_reparticion_i = r5.id_reparticion)
-> Index Scan using pk_repartit on repartit r6
(cost=0.00..6.01 rows=1 width=4)
Index Cond: ("outer".id_reparticion_s =
r6.id_reparticion)
-> Index Scan using pk_carcallt on carcallt l
(cost=0.00..6.01 rows=1 width=44)
Index Cond: ("outer".actuacion_car =
l.actuacion_cal)
->
Index Scan using pk_callest on callest ll9
(cost=0.00..5.98 rows=1 width=4)
Index Cond: ("outer".id_calle_cal = ll9.id_calle)
-> Index
Scan using pk_callest on callest ll10
(cost=0.00..5.98 rows=1 width=4)
Index Cond: ("outer".id_calle1_cal = ll10.id_calle)
-> Index Scan
using pk_callest on callest ll11 (cost=0.00..5.98
rows=1 width=4)
Index
Cond: ("outer".id_calle2_cal = ll11.id_calle)
-> Index Scan using
pk_callest on callest ll12 (cost=0.00..5.98 rows=1
width=4)
Index Cond:
("outer".id_esquina_cal = ll12.id_calle)
-> Index Scan using
pk_pasest on pasest p (cost=0.00..167.43 rows=46
width=40)
Index Cond:
("outer".actuacion_car = p.act_principal)
-> Index Scan using pk_repartit
on repartit r7 (cost=0.00..6.01 rows=1 width=4)
Index Cond:
("outer".id_repart_origen = r7.id_reparticion)
-> Index Scan using pk_repartit on
repartit r8 (cost=0.00..6.01 rows=1 width=4)
Index Cond:
("outer".id_repart_destino = r8.id_reparticion)
-> Index Scan using pk_repartit on repartit
r9 (cost=0.00..6.01 rows=1 width=4)
Index Cond: ("outer".id_reparticion_u =
r9.id_reparticion)
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2005-08-12 16:28 Richard Huxton <dev@archonet.com>
parent: marcelo Cortez <jmdc_marcelo@yahoo.com.ar>
0 siblings, 1 reply; 48+ messages in thread
From: Richard Huxton @ 2005-08-12 16:28 UTC (permalink / raw)
To: marcelo Cortez <jmdc_marcelo@yahoo.com.ar>; +Cc: pgsql-general
marcelo Cortez wrote:
> hi guys
>
> i need advice for query optimization,take too long
> time.
> any coments are welcomed :).
Comment 1 - you'll need to post the output of "EXPLAIN ANALYSE", not
just EXPLAIN, otherwise people can't see how long it took.
Comment 2 - you provide the definition of one table but there are about
20 involved in the query.
Comment 3 - likewise for the size of tables.
Comment 4 - you apply a LIMIT without an ORDER BY. Are you sure this is
what you want?
Comment 5 - you don't show any INDEX definitions, so it's difficult to
know whether they are being used.
Comment 6 - Do you know about the genetic query optimiser and thq
geqo_xxx configuration settings?
HTH - come back with the EXPLAIN ANALYSE etc.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2005-08-13 18:28 marcelo Cortez <jmdc_marcelo@yahoo.com.ar>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 48+ messages in thread
From: marcelo Cortez @ 2005-08-13 18:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-general
hi guys , Richad
you are right!, my apologies
profile data:
postgres: "PostgreSQL 7.4.3 on i386-pc-linux-gnu,
compiled by GCC 2.95.4"
HP proliant
processor: Dual Xeon 3.40 Ghz.
HD: 250 Gigabytes hard disk storage.
responses:
> Comment 4 - you apply a LIMIT without an ORDER BY.
> Are you sure this is
> what you want?
yes i think is to truncate result size on the client
side , one message is send to user.
Comment 6 - Do you know about the genetic query
> optimiser and thq
> geqo_xxx configuration settings?
question about this ,my posgresl.conf is
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on
tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0
is 11 join tables the threshold for activate Genetic
Query Optimizer?
> Comment 5 - you don't show any INDEX definitions, so
> it's difficult to
> know whether they are being used.
ok there is too much data sorry.
table repartit 8000 rows aprox.
CREATE TABLE repartit
(
id_reparticion int4 NOT NULL DEFAULT
nextval('reparticiones'::text),
codigo_reparticion char(9) NOT NULL,
codigo_repar_inter char(9),
nombre_reparticion char(60),
vigencia_desde date NOT NULL,
vigencia_hasta date NOT NULL,
id_calle_repar int4 NOT NULL,
numero char(10) NOT NULL,
piso char(10),
oficina char(10),
telefono char(30),
fax char(30),
email char(30),
codigo_estructura numeric(2) NOT NULL,
repart_presentismo char(16),
id_reparticion_ext int4,
proximo_remito numeric(6) NOT NULL DEFAULT 0,
en_red char(1) NOT NULL DEFAULT ''::bpchar,
sector_mesa char(1) NOT NULL DEFAULT ''::bpchar,
CONSTRAINT pk_repartit PRIMARY KEY (id_reparticion),
CONSTRAINT fx_callest FOREIGN KEY (id_calle_repar)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_estructura FOREIGN KEY
(codigo_estructura)
REFERENCES estructt (codigo_estructura) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT ix1_repartit UNIQUE (codigo_reparticion,
codigo_repar_inter)
)
WITHOUT OIDS;
ALTER TABLE repartit OWNER TO postgres;
GRANT ALL ON TABLE repartit TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE repartit TO GROUP devel;
table pasest huge one 20 millions of records
cREATE TABLE pasest
(
act_principal char(24) NOT NULL,
fecha_inicio timestamp NOT NULL,
act_incorporada char(24) NOT NULL,
codigo_incorporado char(1) NOT NULL,
fecha_fin timestamp,
id_repart_origen int4 NOT NULL,
id_repart_destino int4 NOT NULL,
fojas numeric(4) NOT NULL,
recibo_suelto char(1),
ficha_tramite numeric(6),
numer_remito int4,
id_reparticion_u int4 NOT NULL,
observaciones1 varchar(250),
observaciones2 varchar(250),
cod_permanencia char(2),
estado_pase char(1),
paq_actua_anterior char(1) NOT NULL DEFAULT
''::bpchar,
actua_caratulacion char(1) NOT NULL DEFAULT
''::bpchar,
param_01 char(1) NOT NULL DEFAULT ''::bpchar,
param_02 char(1) NOT NULL DEFAULT ''::bpchar,
param_03 char(1) NOT NULL DEFAULT ''::bpchar,
fts_observaciones tsvector,
comp_ano int4,
comp_nro int4,
comp_imp float4,
cuerpos_anexos varchar,
orden_pago varchar,
comp_tipo varchar,
CONSTRAINT pk_pasest PRIMARY KEY (act_principal,
fecha_inicio, act_incorporada),
CONSTRAINT fk_permanencia FOREIGN KEY
(cod_permanencia)
REFERENCES permanet (codigo_permanencia) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_caratult FOREIGN KEY (act_principal)
REFERENCES caratult (actuacion_car) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_destino FOREIGN KEY
(id_repart_destino)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_id_reparticion_u FOREIGN
KEY (id_reparticion_u)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_origen FOREIGN KEY
(id_repart_origen)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE pasest OWNER TO postgres;
GRANT ALL ON TABLE pasest TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE pasest TO GROUP devel;
indexes
"CREATE INDEX ix2_pasest ON pasest USING btree
(act_incorporada)"
"CREATE INDEX ix3_pasest ON pasest USING btree
(id_repart_origen, numer_remito, fecha_inicio)"
"CREATE INDEX ix4_pasest ON pasest USING btree
(id_repart_destino, fecha_fin)"
"CREATE INDEX ix5_pasest ON pasest USING btree
(id_repart_origen, fecha_inicio)"
"CREATE INDEX ix6_pasest ON pasest USING btree
(cod_permanencia)"
"CREATE INDEX ix7_pasest ON pasest USING btree
(id_reparticion_u)"
"CREATE INDEX ix8_pasest ON pasest USING btree
(numer_remito)"
"CREATE INDEX ix9_pasest ON pasest USING btree
(fecha_inicio)"
"CREATE INDEX ix10_fts_observaciones ON pasest USING
gist (fts_observaciones)"
"CREATE INDEX idx_act_principal_letra ON pasest USING
btree (letra((act_principal)::text))"
"CREATE INDEX idx_act_principal_anio ON pasest USING
btree (anio((act_principal)::text))"
"CREATE INDEX idx_act_principal_secuencia ON pasest
USING btree (secuencia((act_principal)::text))"
"CREATE INDEX idx_act_principal_numero ON pasest USING
btree (numero((act_principal)::text))"
"CREATE INDEX idx_act_principal_reparticion ON pasest
USING btree (repart((act_principal)::text))"
"CREATE INDEX idx_act_principal_all ON pasest USING
btree (letra((act_principal)::text),
anio((act_principal)::text),
numero((act_principal)::text),
secuencia((act_principal)::text),
repart((act_principal)::text))"
"CREATE UNIQUE INDEX pk_pasest ON pasest USING btree
(act_principal, fecha_inicio, act_incorporada)"
table caratult
OP TABLE caratult;
CREATE TABLE caratult 6 millions of records
(
actuacion_car char(24) NOT NULL,
id_reparticion_uc int4 NOT NULL,
fecha_inicio timestamp NOT NULL,
tipo_actuacion char(1) NOT NULL,
id_extracto_car int4,
act_extramunicipal char(35),
observaciones varchar(250),
comentario1 varchar(250) NOT NULL,
comentario2 varchar(250),
comentario3 varchar(250),
si_calle char(1) NOT NULL,
verdadera char(1) NOT NULL,
orden_pago char(10),
fac_tipo char(2),
fac_anio numeric(4),
fac_nro numeric(8),
fac_importe numeric(13),
anexos varchar(50),
recibo_suelto char(1) NOT NULL,
id_actuacion_car int4 NOT NULL,
id_reparticion_car int4 NOT NULL,
id_secuencia_car int4 NOT NULL,
fecha_inicio_real date NOT NULL,
fts_comentario tsvector,
fts_observaciones tsvector,
CONSTRAINT pk_caratult PRIMARY KEY (actuacion_car),
CONSTRAINT fx_actuacit FOREIGN KEY
(id_actuacion_car)
REFERENCES actuacit (id_actuacion) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_extracto FOREIGN KEY (id_extracto_car)
REFERENCES extractt (id_extracto) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_car FOREIGN KEY
(id_reparticion_car)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_uc FOREIGN KEY
(id_reparticion_uc)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_secuencia1 FOREIGN KEY
(id_secuencia_car)
REFERENCES secuenct (id_secuencia) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE caratult OWNER TO postgres;
GRANT ALL ON TABLE caratult TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE caratult TO GROUP devel;
indexes:
"CREATE INDEX ix2_caratult ON caratult USING btree
(id_reparticion_uc)"
"CREATE INDEX ix3_caratult ON caratult USING btree
(id_extracto_car)"
"CREATE INDEX ix4_caratult ON caratult USING btree
(id_reparticion_car)"
"CREATE INDEX ix5_caratult ON caratult USING btree
(verdadera, tipo_actuacion)"
"CREATE INDEX ix6_caratult ON caratult USING btree
(fecha_inicio_real, actuacion_car)"
"CREATE INDEX ix7_caratult ON caratult USING btree
(id_actuacion_car)"
"CREATE INDEX ix8_caratult ON caratult USING btree
(orden_pago)"
"CREATE INDEX ix9_caratult ON caratult USING btree
(fac_tipo, fac_anio, fac_nro)"
"CREATE INDEX ix_fts_comentario ON caratult USING gist
(fts_comentario)"
"CREATE INDEX ""IX10_caratult"" ON caratult USING
btree (actuacion_car)"
"CREATE INDEX ix_fts_observaciones ON caratult USING
gist (fts_observaciones)"
"CREATE INDEX actuacion_car_all ON caratult USING
btree (letra((actuacion_car)::text),
anio((actuacion_car)::text),
numero((actuacion_car)::text),
secuencia((actuacion_car)::text),
repart((actuacion_car)::text))"
"CREATE UNIQUE INDEX pk_caratult ON caratult USING
btree (actuacion_car)"
"CREATE INDEX fki_fx_secuencia1 ON caratult USING
btree (id_secuencia_car)"
"CREATE INDEX ix1_caratult ON caratult USING btree
(actuacion_car)"
"CREATE INDEX ix11_caratult ON caratult USING btree
(id_extracto_car)"
table carcalt 400.000 records
CREATE TABLE carcallt
(
actuacion_cal char(24) NOT NULL,
id_calle_cal int4 NOT NULL,
numero char(10),
edificio char(10),
torre char(10),
piso char(10),
departamento char(10),
escalera char(10),
id_calle1_cal int4,
id_calle2_cal int4,
id_esquina_cal int4,
altura char(10),
parcela char(12),
partida numeric(7),
CONSTRAINT pk_carcallt PRIMARY KEY (actuacion_cal),
CONSTRAINT fx_calle0 FOREIGN KEY (id_calle_cal)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_calle1 FOREIGN KEY (id_calle1_cal)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_calle3 FOREIGN KEY (id_esquina_cal)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_caratult FOREIGN KEY (actuacion_cal)
REFERENCES caratult (actuacion_car) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT id_calle2 FOREIGN KEY (id_calle2_cal)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE carcallt OWNER TO postgres;
GRANT ALL ON TABLE carcallt TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE carcallt TO GROUP devel;
indexes
"CREATE INDEX ix2_carcallt ON carcallt USING btree
(id_calle_cal)"
"CREATE INDEX ix3_carcallt ON carcallt USING btree
(id_calle1_cal)"
"CREATE INDEX ix4_carcallt ON carcallt USING btree
(id_calle2_cal)"
"CREATE INDEX ix5_carcallt ON carcallt USING btree
(id_esquina_cal)"
"CREATE UNIQUE INDEX pk_carcallt ON carcallt USING
btree (actuacion_cal)"
best
MDC
__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query optimization
@ 2005-09-30 22:24 Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 48+ messages in thread
From: Sean Davis @ 2005-09-30 22:24 UTC (permalink / raw)
To: pgsql-general
I have a couple of very large tables that I am querying on that gives the
following explain analyze output. If someone can help out with my mess,
that would be great.
Thanks,
Sean
explain analyze
select e.*,c.*
from u_all_est_mrna c
join g_rna_acc d on c.accession=d.accession,
(select a.gene_id,
b.db_id,
max(tend-tstart)
from g_rna_acc a
join u_all_est_mrna b on a.accession=b.accession
where gene_id<200
group by a.gene_id,b.db_id) e
where abs(tstart-tend)=e.max
and d.gene_id=e.gene_id
and c.db_id=e.db_id;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------
Hash Join (cost=1054997.38..2472083.62 rows=2 width=219) (actual
time=122796.024..357269.576 rows=327 loops=1)
Hash Cond: ((("outer".accession)::text = ("inner".accession)::text) AND
(abs(("outer".tstart - "outer".tend)) = "inner".max) AND
(("outer".db_id)::text = ("inner".db_id)::text))
-> Seq Scan on u_all_est_mrna c (cost=0.00..932582.74 rows=24225174
width=179) (actual time=17.384..302484.904 rows=24225174 loops=1)
-> Hash (cost=1054973.98..1054973.98 rows=3119 width=52) (actual
time=11562.968..11562.968 rows=2276 loops=1)
-> Nested Loop (cost=1046393.15..1054973.98 rows=3119 width=52)
(actual time=11546.931..11558.704 rows=2276 loops=1)
-> HashAggregate (cost=1046393.15..1046395.98 rows=567
width=19) (actual time=11546.892..11547.188 rows=276 loops=1)
-> Nested Loop (cost=4.78..1046285.32 rows=14377
width=19) (actual time=0.148..11537.307 rows=1854 loops=1)
-> Index Scan using g_rna_acc_gene_id on
g_rna_acc a (cost=0.00..1049.44 rows=519 width=16) (actual
time=0.026..50.006 rows=1139 loops=1)
Index Cond: (gene_id < 200)
-> Bitmap Heap Scan on u_all_est_mrna b
(cost=4.78..2007.57 rows=510 width=26) (actual time=7.100..10.068 rows=2
loops=1139)
Recheck Cond: (("outer".accession)::text =
(b.accession)::text)
-> Bitmap Index Scan on uaem_accession
(cost=0.00..4.78 rows=510 width=0) (actual time=4.270..4.270 rows=2
loops=1139)
Index Cond:
(("outer".accession)::text = (b.accession)::text)
-> Index Scan using g_rna_acc_gene_id on g_rna_acc d
(cost=0.00..15.04 rows=6 width=16) (actual time=0.010..0.037 rows=8
loops=276)
Index Cond: (d.gene_id = "outer".gene_id)
Total runtime: 357270.873 ms
(16 rows)
\d+ u_all_est_mrna
Table
"public.u_all_est_mrna"
Column | Type |
Modifiers | Description
-----------------+-------------------+--------------------------------------
---------------------------------------+------------------------------------
all_est_mrna_id | integer | not null default
nextval('public.u_all_est_mrna_all_est_mrna_id_seq'::text) |
db_id | character varying |
|
seqtype | character varying |
|
matches | integer |
|
mismatches | integer |
|
repmatches | integer |
|
ncount | integer |
|
qnuminsert | integer |
|
qbaseinsert | integer |
|
tnuminsert | integer |
|
tbaseinsert | integer |
|
strand | character(1) |
|
accession | character varying |
| Genbank Accession without version.
qsize | integer |
|
qstart | integer |
|
qend | integer |
|
chrom | character varying |
| Chromosome, notation like "chr1"
tsize | integer |
|
tstart | integer |
| Blat hit start
tend | integer |
| Blat hit end
blockcount | integer |
|
blocksizes | character varying |
|
qstarts | character varying |
|
tstarts | character varying |
|
Indexes:
"u_all_est_mrna_pkey" PRIMARY KEY, btree (all_est_mrna_id)
"uaem_accession" btree (accession)
"uaem_chrom" btree (chrom)
"uaem_db_id" btree (db_id)
"uaem_seqtype" btree (seqtype)
"uaem_tend_chrom" btree (tend, chrom)
"uaem_tstart_chrom" btree (tstart, chrom)
Has OIDs: yes
=== psql 78 ===
\d+ g_rna_acc
Table "public.g_rna_acc"
Column | Type | Modifiers | Description
--------------+-------------------+-----------+-------------
gene_id | integer | |
accession | character varying | not null |
version | integer | |
accession_gi | integer | |
Indexes:
"g_rna_acc_pkey" PRIMARY KEY, btree (accession)
"g_rna_acc_accession" btree (accession)
"g_rna_acc_gene_id" btree (gene_id)
Foreign-key constraints:
"g_rna_acc_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES
g_main(gene_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Has OIDs: no
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2005-10-01 15:29 Jim C. Nasby <jnasby@pervasive.com>
parent: Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 48+ messages in thread
From: Jim C. Nasby @ 2005-10-01 15:29 UTC (permalink / raw)
To: Sean Davis <sdavis2@mail.nih.gov>; +Cc: pgsql-general
On Fri, Sep 30, 2005 at 06:24:52PM -0400, Sean Davis wrote:
> -> Seq Scan on u_all_est_mrna c (cost=0.00..932582.74 rows=24225174
> width=179) (actual time=17.384..302484.904 rows=24225174 loops=1)
That step is where most of your time is being spent, which isn't
surprising given that it's scanning 24M rows. An index on
abs(tend-tstart) might help eliminate that. It will also probably help
if you increase the statistics_target for u_all_est_mrna, since it might
be best to join before filtering on abs(tend-tstart), which is the
opposite of what it's doing now.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 48+ messages in thread
* query optimization
@ 2008-07-15 18:42 Kevin Duffy <KD@wrinvestments.com>
0 siblings, 0 replies; 48+ messages in thread
From: Kevin Duffy @ 2008-07-15 18:42 UTC (permalink / raw)
To: pgsql-general
Hello:
I need your kind assistance to debug an optimization issue.
The following two select statements in my book are almost identical.
One does a lookup for security type 'CFD' and
the other does the same lookup except for security 'OP'. When run with
'CFD' the query never returns.
When run with 'OP' the results return almost instantly. If I hard code
'CFD' to be 5 which is the securitytypekey,
the query runs instantly.
The code for getsectypekey() is below. Please note SECURITYTYPE
contains only 28 rows.
Why would these queries run so differently?
Many thanks for taking the time to look at this issue.
KD
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(substring(productid,1,3)) = 'CFD' and
upper(positions_gsco.producttype) = 'OP' and
getsectypekey('CFD') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker = sec.
underlyingbloomberg ) ;
EXPLAIN
"Nested Loop (cost=0.00..2829.87 rows=1 width=374)"
" Join Filter: ((positions_gsco.issuecurrency =
(sec.securityissuecurriso)::bpchar) AND (positions_gsco.strikeprice =
sec.strikeprice) AND (positions_gsco.expirationdate =
sec.expirationdate) AND ((positions_gsco.underlyingisin =
(sec.underlyingisin)::bpchar) OR (positions_gsco.underlyingcusip =
(sec.underlyingcusip)::bpchar) OR
(positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))"
" -> Seq Scan on positions_gsco (cost=0.00..2310.40 rows=1 width=72)"
" Filter: ((securitykey IS NULL) AND
(upper("substring"((productid)::text, 1, 3)) = 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))"
" -> Seq Scan on "security" sec (cost=0.00..504.52 rows=598
width=374)"
" Filter: (getsectypekey('CFD'::bpchar) = securitytypekey)"
** ** ** ** ** ** **
** ** ** ** ** ** **
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(substring(productid,1,3)) != 'CFD' and
upper(positions_gsco.producttype) = 'OP' and
getsectypekey('OP') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.putcallind = sec.put_call and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker =
sec.underlyingbloomberg ) ;
EXPLAIN
"Hash Join (cost=514.99..2861.41 rows=1 width=374)"
" Hash Cond: ((positions_gsco.issuecurrency =
(sec.securityissuecurriso)::bpchar) AND (positions_gsco.putcallind =
sec.put_call) AND (positions_gsco.expirationdate = sec.expirationdate))"
" Join Filter: ((positions_gsco.strikeprice = sec.strikeprice) AND
((positions_gsco.underlyingisin = (sec.underlyingisin)::bpchar) OR
(positions_gsco.underlyingcusip = (sec.underlyingcusip)::bpchar) OR
(positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))"
" -> Seq Scan on positions_gsco (cost=0.00..2310.40 rows=16
width=79)"
" Filter: ((securitykey IS NULL) AND
(upper("substring"((productid)::text, 1, 3)) <> 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))"
" -> Hash (cost=504.52..504.52 rows=598 width=374)"
" -> Seq Scan on "security" sec (cost=0.00..504.52 rows=598
width=374)"
" Filter: (getsectypekey('OP'::bpchar) = securitytypekey)"
** ** ** ** ** ** **
** ** ** ** ** ** **
CREATE OR REPLACE FUNCTION getsectypekey(sectype_in bpchar)
RETURNS integer AS
$BODY$
declare
sectypekey integer;
begin
select securitytypekey into sectypekey
from securitytype
where position (upper('|' || sectype_in || '|' ) in
upper(securitytypeaka) ) > 0;
--
-- did not find a type above
if sectypekey is NULL then
select securitytypekey into sectypekey
from securitytype
where upper(securitytypeshort) = 'UNKNOWN';
end if;
return sectypekey;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getsectypekey(sectype_in bpchar) OWNER TO postgres;
Kevin Duffy
^ permalink raw reply [nested|flat] 48+ messages in thread
* query optimization
@ 2008-07-17 15:56 Kevin Duffy <KD@wrinvestments.com>
0 siblings, 2 replies; 48+ messages in thread
From: Kevin Duffy @ 2008-07-17 15:56 UTC (permalink / raw)
To: pgsql-general
Hello:
This posting is a follow up to this posting from July 15th.
http://archives.postgresql.org/pgsql-general/2008-07/msg00569.php
Given the following select statement
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(positions_gsco.producttype) = 'OP' and
upper(substring(productid,1,3)) = 'CFD' and
getsectypekey('CFD') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker = sec.
underlyingbloomberg ) ;
Run as is this statement never returns .
Additional info:
select * from positions_gsco
where ((securitykey IS NULL) AND
(upper("substring"((productid)::text, 1, 3)) = 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey =
359))
Returns 2538 rows in 1625ms
select * from security
where (getsectypekey('CFD'::bpchar) = securitytypekey);
returns 2538 rows in 1078ms
so we are not dealing with very large datasets.
Security has an index defined as follows:
CREATE INDEX security_sectypekey ON "security" USING btree
(securitytypekey);
These is a total of 11443 rows in security.
If I change the getsectypekey('CFD') in the above statement to be
either ' (select getsectypekey('CFD') ) ' or '5'
I get 2632 rows in approx 4300ms. There may be an issue here with
number of rows returned. I'll look into that.
But the point is it returns in a reasonable number of seconds.
So here are the questions for the PSQL gurus:
Is getsectypekey('CFD') executing for every join (or possible join)
between positions_gsco and security?
Causing a scan of security for every possible join.
Does ' (select getsectypekey('CFD') ) ' cause the getsectype()
function to be executed once and thus
allowing the index on security to be used.
And of couse '5' makes things simple. The index on security is used.
Am I posting this in the right. If not please help me correct my error
and point me to the correct spot.
Thanks for taking a look at my issue.
Best Regards
Kevin Duffy
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2008-07-17 18:00 Scott Marlowe <scott.marlowe@gmail.com>
parent: Kevin Duffy <KD@wrinvestments.com>
1 sibling, 0 replies; 48+ messages in thread
From: Scott Marlowe @ 2008-07-17 18:00 UTC (permalink / raw)
To: Kevin Duffy <KD@wrinvestments.com>; +Cc: pgsql-general
What does the output of explain select sec.* ... have to say?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2008-07-17 23:41 Klint Gore <kgore4@une.edu.au>
parent: Kevin Duffy <KD@wrinvestments.com>
1 sibling, 1 reply; 48+ messages in thread
From: Klint Gore @ 2008-07-17 23:41 UTC (permalink / raw)
To: Kevin Duffy <KD@wrinvestments.com>; +Cc: pgsql-general
Kevin Duffy wrote:
>
> So here are the questions for the PSQL gurus:
>
> Is getsectypekey(‘CFD’) executing for every join (or possible join)
> between positions_gsco and security?
>
> Causing a scan of security for every possible join.
>
> Does ‘ (select getsectypekey('CFD') ) ‘ cause the getsectype()
> function to be executed once and thus
>
> allowing the index on security to be used.
>
> And of couse ‘5’ makes things simple. The index on security is used.
>
> Am I posting this in the right. If not please help me correct my error
> and point me to the correct spot.
>
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: query optimization
@ 2008-07-18 14:39 Kevin Duffy <KD@wrinvestments.com>
parent: Klint Gore <kgore4@une.edu.au>
0 siblings, 0 replies; 48+ messages in thread
From: Kevin Duffy @ 2008-07-18 14:39 UTC (permalink / raw)
To: pgsql-general; Klint Gore <kgore4@une.edu.au>
I sent this follow up in yesterday, but it did not show up.
Must be doing something wrong. Here is the second try.
kd
select * from security sec
where getsectypekey('OP') = sec.securitytypekey returns 690 rows
in 1625ms EXPLAIN "Seq Scan on "security" sec (cost=0.00..507.54
rows=602 width=374)"
" Filter: (getsectypekey('OP'::bpchar) = securitytypekey)"
select * from security sec
where ( select getsectypekey('OP') ) = sec.securitytypekey
returns 690 rows in 172ms
EXPLAIN
"Bitmap Heap Scan on "security" sec (cost=16.93..368.36 rows=602
width=374)"
" Recheck Cond: ($0 = securitytypekey)"
" InitPlan"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Bitmap Index Scan on security_sectypekey (cost=0.00..16.77
rows=602 width=0)"
" Index Cond: ($0 = securitytypekey)"
So this proves it is using the index.
But I think the issue is in the interaction between the numbers of rows
returned from positions_gsco and security.
kd
-----Original Message-----
From: Klint Gore [mailto:kgore4@une.edu.au]
Sent: Thursday, July 17, 2008 7:41 PM
To: Kevin Duffy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] query optimization
Kevin Duffy wrote:
>
> So here are the questions for the PSQL gurus:
>
> Is getsectypekey('CFD') executing for every join (or possible join)
> between positions_gsco and security?
>
> Causing a scan of security for every possible join.
>
> Does ' (select getsectypekey('CFD') ) ' cause the getsectype()
> function to be executed once and thus
>
> allowing the index on security to be used.
>
> And of couse '5' makes things simple. The index on security is used.
>
> Am I posting this in the right. If not please help me correct my error
> and point me to the correct spot.
>
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query optimization
@ 2012-04-11 10:28 nissyre@wp.pl
0 siblings, 1 reply; 48+ messages in thread
From: nissyre@wp.pl @ 2012-04-11 10:28 UTC (permalink / raw)
To: pgsql-general
Hi,
I'm looking for materials about query optimization in PostgreSQL. I read documentation but it doesnt contain much information. Can you recommend a good book or website when i could learn something about this?
thanks in advance for your help
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2012-04-13 13:07 Sergey Konoplev <gray.ru@gmail.com>
parent: nissyre@wp.pl
0 siblings, 0 replies; 48+ messages in thread
From: Sergey Konoplev @ 2012-04-13 13:07 UTC (permalink / raw)
To: nissyre@wp.pl; +Cc: pgsql-general
Hi,
On Wed, Apr 11, 2012 at 2:28 PM, <nissyre@wp.pl> wrote:
> I'm looking for materials about query optimization in PostgreSQL. I read documentation but it doesnt contain much information. Can you recommend a good book or website when i could learn something about this?
I think you can find some good info about it here
http://wiki.postgresql.org/wiki/Performance_Optimization
>
> thanks in advance for your help
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru@gmail.com Skype: gray-hemp
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query optimization
@ 2014-10-29 01:03 Jorge Arevalo <jorgearevalo@libregis.org>
0 siblings, 2 replies; 48+ messages in thread
From: Jorge Arevalo @ 2014-10-29 01:03 UTC (permalink / raw)
To: pgsql-general
Hello,
I'm trying to optimize a query that takes too much time. This is what I have
table1(field1, field2... field14): contains about 8.5 million rows
table2(f1, f2, f3): contains about 2.5 million rows
table3: is empty, and must be filled with data coming from table1 and table2
To fill table3, I'm using a query that looks like this:
WITH
records_to_insert(field1,field2,field3,field4,field5,field6_hstore,field7,field8_array)
AS
(SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
field10, field11, field12, field13, field14) as metadata, value7, (select
array((select row(f1, f2) from table2 p where p.field7 = field7))) as
values_array FROM table1)
SELECT
fill_table3_function(field1,field2,field3,field4,field5,field6_hstore,field7,field8_array)
FROM records_to_insert
So, I first generate a common table records_to_insert, using data from
table1 and table2, and then call a function fill_table3_function, in order
to insert the values into table3 (I do more things apart from insert,
that's reason to call a function instead of just raising an insert query).
There are indexes created on all the columns that need them.
I'm having problems because the query takes a lot of time, and the server
returns a timeout error.
I think the bottleneck is the array built for the last argument of my
common table, and maybe the hstore too (not sure if it's a heavy process or
not). First thing I've tried is to limit the query to build the common
table, using LIMIT .. OFFSET after 'FROM table1', and make a loop to seek
table1 (the big one, with 8 million rows). But still getting timeout errors.
The problem is I don't have access to Postgres configuration, in order to
increase the timeout for user queries. And anyway, I don't think that
increasing the timeout is a real solution (It'll just make the server
suffer for more time).
So, is there anything obviously wrong with my query? Any changes to make it
faster?
Many thanks in advance, and best regards,
--
Jorge Arevalo
Freelance developer
http://about.me/jorgeas80
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 01:46 David G Johnston <david.g.johnston@gmail.com>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 0 replies; 48+ messages in thread
From: David G Johnston @ 2014-10-29 01:46 UTC (permalink / raw)
To: pgsql-general
Jorge Arévalo-2 wrote
> So, I first generate a common table records_to_insert, using data from
> table1 and table2, and then call a function fill_table3_function, in order
> to insert the values into table3 (I do more things apart from insert,
> that's reason to call a function instead of just raising an insert query).
> There are indexes created on all the columns that need them.
I would suggest you figure out how to do what you need without pushing the
insert into the function.
> So, is there anything obviously wrong with my query? Any changes to make
> it
> faster?
You shouldn't expect much useful help when the important part of your query
is not provided.
Creating arrays and hstores is expensive but whether that is the biggest
factor is impossible to tell.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-optimization-tp5824739p5824744.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 01:54 David G Johnston <david.g.johnston@gmail.com>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 1 reply; 48+ messages in thread
From: David G Johnston @ 2014-10-29 01:54 UTC (permalink / raw)
To: pgsql-general
Jorge Arévalo-2 wrote
> (SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14) as metadata, value7, (select
> array((select row(f1, f2) from table2 p where p.field7 = field7))) as
> values_array FROM table1)
You might try seeing whether:
FROM table1 JOIN (
SELECT field7, array_agg(row(f1, f2)) AS values_array FROM table2 GROUP BY
field7
) tbl2_agg USING (field7)
helps...
I'm also dubious (though this isn't necessarily a performance issue) of:
array[...] AS metadata
Without context I would say this would be better as a composite type instead
of an array. You may find it useful to use named composite types elsewhere
too...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-optimization-tp5824739p5824746.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 18:06 Jorge Arevalo <jorgearevalo@libregis.org>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 48+ messages in thread
From: Jorge Arevalo @ 2014-10-29 18:06 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general
Hello David, many thanks for your responses,
Sorry for not providing the content of the fill_table3_function, but it
just executes 3 insert queries in 3 different tables. And I've checked the
time consuming operation is in this query (by the way, there was a little
mistake in the name of the fields of the inner select, I've corrected it)
SELECT value1,value2,value3,value4,
value5, hstore(ARRAY['field9', 'field10', 'field11', 'field12', 'field13',
'field14'], ARRAY[field9, field10, field11, field12, field13, field14) as
metadata, value7, (select array((select row(f1, f2) from table2 p where
p.f3 = field7))) as values_array FROM table1
This is the result of EXPLAIN ANALYZE
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
loops=1)
InitPlan 2 (returns $1)
-> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
time=7009.063..7009.065 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
Filter: (f3 = field7)
So, there's a sequential scan over table2 (in the query to get
values_array), instead of a index scan. Could it be because the SELECT
returns more than approximately 5-10% of all rows in the table? (I've heard
that, under those conditions, a sequential scan is faster than index scan,
because the amount of I/O operations required for each row)
Anyway, if I understood well, I should try:
- Avoiding that inner query by using a JOIN instead
- Return a composite type instead of an array
Am I right? What kind of additional context information would you need?
Many thanks!!
--
Jorge
On Wed, Oct 29, 2014 at 2:54 AM, David G Johnston <
david.g.johnston@gmail.com> wrote:
> Jorge Arévalo-2 wrote
> > (SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
> > 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> > field10, field11, field12, field13, field14) as metadata, value7, (select
> > array((select row(f1, f2) from table2 p where p.field7 = field7))) as
> > values_array FROM table1)
>
> You might try seeing whether:
>
> FROM table1 JOIN (
> SELECT field7, array_agg(row(f1, f2)) AS values_array FROM table2 GROUP BY
> field7
> ) tbl2_agg USING (field7)
>
> helps...
>
> I'm also dubious (though this isn't necessarily a performance issue) of:
>
> array[...] AS metadata
>
> Without context I would say this would be better as a composite type
> instead
> of an array. You may find it useful to use named composite types elsewhere
> too...
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Query-optimization-tp5824739p5824746.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Jorge Arevalo
Freelance developer
http://about.me/jorgeas80
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 18:24 David Johnston <david.g.johnston@gmail.com>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 0 replies; 48+ messages in thread
From: David Johnston @ 2014-10-29 18:24 UTC (permalink / raw)
To: Jorge Arevalo <jorgearevalo@libregis.org>; +Cc: pgsql-general
List preference is to inline post or, at worse, bottom post. Please do not
top post.
On Wed, Oct 29, 2014 at 11:06 AM, Jorge Arevalo <jorgearevalo@libregis.org>
wrote:
> Hello David, many thanks for your responses,
>
> Sorry for not providing the content of the fill_table3_function, but it
> just executes 3 insert queries in 3 different tables. And I've checked the
> time consuming operation is in this query (by the way, there was a little
> mistake in the name of the fields of the inner select, I've corrected it)
>
> SELECT value1,value2,value3,value4,
> value5, hstore(ARRAY['field9', 'field10', 'field11', 'field12', 'field13',
> 'field14'], ARRAY[field9, field10, field11, field12, field13, field14) as
> metadata, value7, (select array((select row(f1, f2) from table2 p where
> p.f3 = field7))) as values_array FROM table1
>
> This is the result of EXPLAIN ANALYZE
>
> QUERY
> PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
> rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
> loops=1)
> InitPlan 2 (returns $1)
> -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
> time=7009.063..7009.065 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
> width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
> Filter: (f3 = field7)
>
> So, there's a sequential scan over table2 (in the query to get
> values_array), instead of a index scan. Could it be because the SELECT
> returns more than approximately 5-10% of all rows in the table? (I've heard
> that, under those conditions, a sequential scan is faster than index scan,
> because the amount of I/O operations required for each row)
>
What concerns me here is that the rows estimate on that table2 scan is
~13k while the actual count is ~ 2.5M; you need to run ANALYZE on both
tables and see if your get similar results. Though given the need for
sequential scan regardless (see next comment) the estimate miss likely
doesn't affect actual performance or the plan that is chosen. But it is
still worth looking into.
>
> Anyway, if I understood well, I should try:
>
> - Avoiding that inner query by using a JOIN instead
>
I don't know...looking at your explain (and some reasoning) it looks as if
it is already doing that for you since there is only a single loop for the
InitPlan 1. This is a little beyond my comfort zone but you've now
provided a decent amount of information for others to speculate...though it
would help to enable various timings as well and try and run the full query
(with the function) in a development environment so that the entire routine
can be evaluated.
> - Return a composite type instead of an array
>
>
Worth looking into but impossible to recommend without knowing what your
make believe fields are and are used for. More style than performance
since I do not know the relative costs of building up an array and creating
a composite.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 18:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 1 reply; 48+ messages in thread
From: Tom Lane @ 2014-10-29 18:53 UTC (permalink / raw)
To: Jorge Arevalo <jorgearevalo@libregis.org>; +Cc: David G Johnston <david.g.johnston@gmail.com>; pgsql-general
Jorge Arevalo <jorgearevalo@libregis.org> writes:
> SELECT value1,value2,value3,value4,
> value5, hstore(ARRAY['field9', 'field10', 'field11', 'field12', 'field13',
> 'field14'], ARRAY[field9, field10, field11, field12, field13, field14) as
> metadata, value7, (select array((select row(f1, f2) from table2 p where
> p.f3 = field7))) as values_array FROM table1
The parentheses/brackets don't seem to match up real well here ...
> This is the result of EXPLAIN ANALYZE
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
> rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
> loops=1)
> InitPlan 2 (returns $1)
> -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
> time=7009.063..7009.065 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
> width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
> Filter: (f3 = field7)
Hm. If I'm reading that right, you're building an array containing
2537787 entries, each of which is a composite datum containing two
columns of unmentioned datatypes. I suspect a big chunk of your
runtime is going into manipulating that array -- PG is not terribly
efficient with big arrays containing variable-width values.
I'm also a bit confused as to why the planner is saying that the (SELECT
ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
"field7" in the innermost WHERE clause is not a reference to table1 but a
reference to table2. Is that really what you meant? IOW, are you sure
this query is performing the right calculation in the first place?
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 19:05 David Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 48+ messages in thread
From: David Johnston @ 2014-10-29 19:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jorge Arevalo <jorgearevalo@libregis.org>; pgsql-general
On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jorge Arevalo <jorgearevalo@libregis.org> writes:
>
> > This is the result of EXPLAIN ANALYZE
>
> > QUERY
> > PLAN
> >
> -------------------------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
> > rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
> > loops=1)
> > InitPlan 2 (returns $1)
> > -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
> > time=7009.063..7009.065 rows=1 loops=1)
> > InitPlan 1 (returns $0)
> > -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
> > width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
> > Filter: (f3 = field7)
>
> Hm. If I'm reading that right, you're building an array containing
> 2537787 entries, each of which is a composite datum containing two
> columns of unmentioned datatypes. I suspect a big chunk of your
> runtime is going into manipulating that array -- PG is not terribly
> efficient with big arrays containing variable-width values.
>
> I'm also a bit confused as to why the planner is saying that the (SELECT
> ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
> "field7" in the innermost WHERE clause is not a reference to table1 but a
> reference to table2. Is that really what you meant? IOW, are you sure
> this query is performing the right calculation in the first place?
>
>
I thought the InitPlan was in place because the planner choose to execute
the correlated subquery as a standalone query since it realizes that it is
going to have to end up processing the entire table anyway due to the lack
of a filter on the outer query. In effect executing "table1 JOIN (table2
subquery) ON (f3 = field7)".
David J.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 19:14 Jorge Arevalo <jorgearevalo@libregis.org>
parent: David Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 48+ messages in thread
From: Jorge Arevalo @ 2014-10-29 19:14 UTC (permalink / raw)
To: David Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
On Wed, Oct 29, 2014 at 8:05 PM, David Johnston <david.g.johnston@gmail.com>
wrote:
> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Jorge Arevalo <jorgearevalo@libregis.org> writes:
>>
>> > This is the result of EXPLAIN ANALYZE
>>
>> > QUERY
>> > PLAN
>> >
>> -------------------------------------------------------------------------------------------------------------------------------------------------
>> > Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
>> > rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
>> > loops=1)
>> > InitPlan 2 (returns $1)
>> > -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
>> > time=7009.063..7009.065 rows=1 loops=1)
>> > InitPlan 1 (returns $0)
>> > -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
>> > width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
>> > Filter: (f3 = field7)
>>
>> Hm. If I'm reading that right, you're building an array containing
>> 2537787 entries, each of which is a composite datum containing two
>> columns of unmentioned datatypes. I suspect a big chunk of your
>> runtime is going into manipulating that array -- PG is not terribly
>> efficient with big arrays containing variable-width values.
>>
>>
The seq scan over table2 is for finding entries in table2 (which contains
2537787) that matches a condition using a column from table1 (entries that
match table1.field7 = table2.f3). But the array isn't going to contain all
the entries, Just a few of them.
I think the time is being used in scanning table2 for all the rows of
table1 (plus than 8 million).
> I'm also a bit confused as to why the planner is saying that the (SELECT
>> ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
>> "field7" in the innermost WHERE clause is not a reference to table1 but a
>> reference to table2. Is that really what you meant? IOW, are you sure
>> this query is performing the right calculation in the first place?
>>
>>
> I thought the InitPlan was in place because the planner choose to execute
> the correlated subquery as a standalone query since it realizes that it is
> going to have to end up processing the entire table anyway due to the lack
> of a filter on the outer query. In effect executing "table1 JOIN (table2
> subquery) ON (f3 = field7)".
>
> David J.
>
>
Yes, for each row of table1, table2 is being scanned, to find all the
entries that satisfy table1.field7 = table2.f3. Sounds that a really heavy
task. I guess I should avoid it, right?
BTW, Tom, this is the query with all the parentheses/brackets
SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
field10, field11, field12, field13, field14]) as metadata, value7, (select
array((select row(f1, f2) from table2 p where p.f3 = field7))) as
values_array FROM table1
Oh, and sorry for the top posting!
--
Jorge Arevalo
Freelance developer
http://about.me/jorgeas80
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 19:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 0 replies; 48+ messages in thread
From: Tom Lane @ 2014-10-29 19:39 UTC (permalink / raw)
To: Jorge Arevalo <jorgearevalo@libregis.org>; +Cc: David Johnston <david.g.johnston@gmail.com>; pgsql-general
Jorge Arevalo <jorgearevalo@libregis.org> writes:
>> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I'm also a bit confused as to why the planner is saying that the (SELECT
>>> ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
>>> "field7" in the innermost WHERE clause is not a reference to table1 but a
>>> reference to table2. Is that really what you meant? IOW, are you sure
>>> this query is performing the right calculation in the first place?
> Yes, for each row of table1, table2 is being scanned, to find all the
> entries that satisfy table1.field7 = table2.f3.
You might think that's what it's doing, but the plan shape says otherwise.
An array of 2537787 entries is being formed *once* and then referenced at
each row of table1. I'd take another look and see if "field7" isn't a
column name that exists in both tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 19:47 David Johnston <david.g.johnston@gmail.com>
parent: Jorge Arevalo <jorgearevalo@libregis.org>
1 sibling, 1 reply; 48+ messages in thread
From: David Johnston @ 2014-10-29 19:47 UTC (permalink / raw)
To: Jorge Arevalo <jorgearevalo@libregis.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo <jorgearevalo@libregis.org>
wrote:
>
> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14]) as metadata, value7, (select
> array((select row(f1, f2) from table2 p where p.f3 = field7))) as
> values_array FROM table1
>
More generally, you really should table-prefix all column in correlated
subqueries.
[...] from table2 p where p.f3 = table1.field7 [...]
I guess the InitPlan 1 you showed simply scanned table2 and applied the
filter which then was fed to InitPlan 2 where the array is built; that
array then is inserted into the outer query ~8M times...
David J.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2014-10-29 20:26 Jorge Arevalo <jorgearevalo@libregis.org>
parent: David Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 48+ messages in thread
From: Jorge Arevalo @ 2014-10-29 20:26 UTC (permalink / raw)
To: David Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
On Wed, Oct 29, 2014 at 8:47 PM, David Johnston <david.g.johnston@gmail.com>
wrote:
> On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo <jorgearevalo@libregis.org
> > wrote:
>
>>
>> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
>> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
>> field10, field11, field12, field13, field14]) as metadata, value7, (select
>> array((select row(f1, f2) from table2 p where p.f3 = field7))) as
>> values_array FROM table1
>>
>
> More generally, you really should table-prefix all column in correlated
> subqueries.
>
> [...] from table2 p where p.f3 = table1.field7 [...]
>
> I guess the InitPlan 1 you showed simply scanned table2 and applied the
> filter which then was fed to InitPlan 2 where the array is built; that
> array then is inserted into the outer query ~8M times...
>
> David J.
>
>
Wow, you were right! There was a field with same name in both tables, and
that caused problems. I've just prefixed each field with the table
identifier, and now it works really fast.
Many thanks, guys!
--
Jorge Arevalo
Freelance developer
http://about.me/jorgeas80
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query optimization
@ 2025-03-13 19:12 Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 2 replies; 48+ messages in thread
From: Durgamahesh Manne @ 2025-03-13 19:12 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi Team
This query takes more time than usual for execution
How to optimize it in best possible way
Columns used in this query >> composite index eventhough not running
optimally
SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId =
$2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId =
$6 AND MarketId = $7 LIMIT ?)
Regards
Durga Mahesh
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-13 19:17 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 48+ messages in thread
From: Adrian Klaver @ 2025-03-13 19:17 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 3/13/25 12:12, Durgamahesh Manne wrote:
> Hi Team
>
> This query takes more time than usual for execution
Define usual.
> How to optimize it in best possible way
Can't be answered without, to start:
1) Postgres version.
2) Complete(including indexes) table schema.
3) Output of EXPLAIN ANALYZE of query.
4) In what client is this being run?
>
> Columns used in this query >> composite index eventhough not running
> optimally
>
>
>
> SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId
> = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND
> EventId = $6 AND MarketId = $7 LIMIT ?)
>
> Regards
> Durga Mahesh
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-13 19:34 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 0 replies; 48+ messages in thread
From: Laurenz Albe @ 2025-03-13 19:34 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, 2025-03-14 at 00:42 +0530, Durgamahesh Manne wrote:
> This query takes more time than usual for execution
>
> How to optimize it in best possible way
>
> Columns used in this query >> composite index eventhough not running optimally
> SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND
> BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND
> EventId = $6 AND MarketId = $7 LIMIT ?)
"The best possible way" is hard to say, because an index will have negative
effects on data modification statements.
But for the fastest execution of the query, simply create an index on all the
columns that occur in the WHERE clause. The order doesn't matter.
And remove that useless LIMIT clause.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 02:18 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 48+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 02:18 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>; laurenz.albe@cybertec.at
On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 3/13/25 12:12, Durgamahesh Manne wrote:
> > Hi Team
> >
> > This query takes more time than usual for execution
>
> Define usual.
>
>
> > How to optimize it in best possible way
>
> Can't be answered without, to start:
>
> 1) Postgres version.
>
> 2) Complete(including indexes) table schema.
>
> 3) Output of EXPLAIN ANALYZE of query.
>
> 4) In what client is this being run?
>
>
> >
> > Columns used in this query >> composite index eventhough not running
> > optimally
> >
> >
> >
> > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId
> > = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND
> > EventId = $6 AND MarketId = $7 LIMIT ?)
> >
> > Regards
> > Durga Mahesh
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Hi Adrian Klaver
1) Postgres version.
select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-6), 64-bit
2) Complete(including indexes) table schema.
Table
"liveaggregations.cachekeys"
Column | Type | Collation | Nullable | Default |
Storage | Compression
---------------+------------------------+-----------+----------+---------+----------+------------
cachetype | character varying(255) | | | |
extended |
trsid | character varying(255) | | | |
extended |
brandid | character varying(255) | | | |
extended |
sportid | character varying(255) | | | |
extended |
competitionid | character varying(255) | | | |
extended |
eventid | character varying(255) | | | |
extended |
marketid | character varying(255) | | | |
extended |
selectionid | character varying(255) | | | |
extended |
keytype | character varying(255) | | | |
extended |
key | character varying(255) | | not null | |
extended |
Indexes:
"cachekeys_key_pk" PRIMARY KEY, btree (key)
"idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
competitionid, eventid, marketid)
"idx_marketid" btree (marketid)
3) Output of EXPLAIN ANALYZE of query.
Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80
rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
'U-523596'::text))
Heap Fetches: 0
Planning Time: 0.221 ms
Execution Time: 0.046 ms
4) In what client is this being run?
betting application
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 02:34 Rob Sargent <robjsargent@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 48+ messages in thread
From: Rob Sargent @ 2025-03-14 02:34 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>; laurenz.albe@cybertec.at
>
> 3) Output of EXPLAIN ANALYZE of query.
>
> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = 'U-523596'::text))
> Heap Fetches: 0
> Planning Time: 0.221 ms
> Execution Time: 0.046 ms
>
And is the explain analyze output if from your original query:
"SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND MarketId = $7 LIMIT ?)”
Has there been any significant addition of rows recently? i.e. are the statistics up-to-date for that table?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 02:39 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 02:39 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>; laurenz.albe@cybertec.at
On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <robjsargent@gmail.com> wrote:
>
>
>
> 3) Output of EXPLAIN ANALYZE of query.
>
> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1
> loops=1)
> InitPlan 1 (returns $0)
> -> Index Only Scan using idx_cachekeys on cachekeys
> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
> 'U-523596'::text))
> Heap Fetches: 0
> Planning Time: 0.221 ms
> Execution Time: 0.046 ms
>
>
>
> And is the explain analyze output if from your original query:
> "SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId
> = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId =
> $6 AND MarketId = $7 LIMIT ?)”
>
> Has there been any significant addition of rows recently? i.e. are the
> statistics up-to-date for that table?
>
Hi
Gave you plan with out limit . Stats up to date insert .....on conflict do
nothing runs some times
Regards
Durga Mahesh
>
>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 02:49 Rob Sargent <robjsargent@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 0 replies; 48+ messages in thread
From: Rob Sargent @ 2025-03-14 02:49 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>; laurenz.albe@cybertec.at
> On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>
>
>
> On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <robjsargent@gmail.com <mailto:robjsargent@gmail.com>> wrote:
>
> Hi
>
> Gave you plan with out limit . Stats up to date insert .....on conflict do nothing runs some times
>
> Regards
> Durga Mahesh
>
>
But still using the “select exists”, thereby turning the result into a true/false value? I do not follow your point on the usage of “on conflict”. Is this also something whose behaviour has changed recently? I assume it references the same table as the original sql?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 02:49 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 48+ messages in thread
From: Ron Johnson @ 2025-03-14 02:49 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
[snip]
> Hi Adrian Klaver
>
> 1) Postgres version.
> select version();
> version
>
> ---------------------------------------------------------------------------------------------------------------
> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>
> 2) Complete(including indexes) table schema.
>
> Table
> "liveaggregations.cachekeys"
> Column | Type | Collation | Nullable | Default |
> Storage | Compression
>
> ---------------+------------------------+-----------+----------+---------+----------+------------
> cachetype | character varying(255) | | | |
> extended |
> trsid | character varying(255) | | | |
> extended |
> brandid | character varying(255) | | | |
> extended |
> sportid | character varying(255) | | | |
> extended |
> competitionid | character varying(255) | | | |
> extended |
> eventid | character varying(255) | | | |
> extended |
> marketid | character varying(255) | | | |
> extended |
> selectionid | character varying(255) | | | |
> extended |
> keytype | character varying(255) | | | |
> extended |
> key | character varying(255) | | not null | |
> extended |
> Indexes:
> "cachekeys_key_pk" PRIMARY KEY, btree (key)
> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
> competitionid, eventid, marketid)
> "idx_marketid" btree (marketid)
>
> 3) Output of EXPLAIN ANALYZE of query.
>
> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1
> loops=1)
> InitPlan 1 (returns $0)
> -> Index Only Scan using idx_cachekeys on cachekeys
> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
> 'U-523596'::text))
> Heap Fetches: 0
> Planning Time: 0.221 ms
> Execution Time: 0.046 ms
>
That looks pretty reasonable.
1. Now show what happens with the LIMIT clause.
2. How many rows does it return?
3. Do you keep the table regularly vacuumed and analyzed?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:27 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 03:27 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general; pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
> [snip]
>
>> Hi Adrian Klaver
>>
>> 1) Postgres version.
>> select version();
>> version
>>
>> ---------------------------------------------------------------------------------------------------------------
>> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
>> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>>
>> 2) Complete(including indexes) table schema.
>>
>> Table
>> "liveaggregations.cachekeys"
>> Column | Type | Collation | Nullable | Default
>> | Storage | Compression
>>
>> ---------------+------------------------+-----------+----------+---------+----------+------------
>> cachetype | character varying(255) | | |
>> | extended |
>> trsid | character varying(255) | | |
>> | extended |
>> brandid | character varying(255) | | |
>> | extended |
>> sportid | character varying(255) | | |
>> | extended |
>> competitionid | character varying(255) | | |
>> | extended |
>> eventid | character varying(255) | | |
>> | extended |
>> marketid | character varying(255) | | |
>> | extended |
>> selectionid | character varying(255) | | |
>> | extended |
>> keytype | character varying(255) | | |
>> | extended |
>> key | character varying(255) | | not null |
>> | extended |
>> Indexes:
>> "cachekeys_key_pk" PRIMARY KEY, btree (key)
>> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
>> competitionid, eventid, marketid)
>> "idx_marketid" btree (marketid)
>>
>> 3) Output of EXPLAIN ANALYZE of query.
>>
>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030
>> rows=1 loops=1)
>> InitPlan 1 (returns $0)
>> -> Index Only Scan using idx_cachekeys on cachekeys
>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>> 'U-523596'::text))
>> Heap Fetches: 0
>> Planning Time: 0.221 ms
>> Execution Time: 0.046 ms
>>
>
> That looks pretty reasonable.
>
> 1. Now show what happens with the LIMIT clause.
> 2. How many rows does it return?
> 3. Do you keep the table regularly vacuumed and analyzed?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hey Ron
1. Now show what happens with the LIMIT clause.
and result set of query and *Size of the table 287MB*
exists
--------
t
(1 row)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80
rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
'U-523596'::text))
Heap Fetches: 1
Planning Time: 0.084 ms
Execution Time: 0.043 ms
(7 rows)
2. How many rows does it return?
One row exists
--------
t
(1 row)
3. Do you keep the table regularly vacuumed and analyzed?
Auto vacuum already in place along with periodic maintenance activity such
as vacuum and analyze runs daily once
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:40 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Ron Johnson @ 2025-03-14 03:40 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
>> maheshpostgres9@gmail.com> wrote:
>> [snip]
>>
>>> Hi Adrian Klaver
>>>
>>> 1) Postgres version.
>>> select version();
>>> version
>>>
>>> ---------------------------------------------------------------------------------------------------------------
>>> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
>>> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>>>
>>> 2) Complete(including indexes) table schema.
>>>
>>> Table
>>> "liveaggregations.cachekeys"
>>> Column | Type | Collation | Nullable | Default
>>> | Storage | Compression
>>>
>>> ---------------+------------------------+-----------+----------+---------+----------+------------
>>> cachetype | character varying(255) | | |
>>> | extended |
>>> trsid | character varying(255) | | |
>>> | extended |
>>> brandid | character varying(255) | | |
>>> | extended |
>>> sportid | character varying(255) | | |
>>> | extended |
>>> competitionid | character varying(255) | | |
>>> | extended |
>>> eventid | character varying(255) | | |
>>> | extended |
>>> marketid | character varying(255) | | |
>>> | extended |
>>> selectionid | character varying(255) | | |
>>> | extended |
>>> keytype | character varying(255) | | |
>>> | extended |
>>> key | character varying(255) | | not null |
>>> | extended |
>>> Indexes:
>>> "cachekeys_key_pk" PRIMARY KEY, btree (key)
>>> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
>>> competitionid, eventid, marketid)
>>> "idx_marketid" btree (marketid)
>>>
>>> 3) Output of EXPLAIN ANALYZE of query.
>>>
>>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030
>>> rows=1 loops=1)
>>> InitPlan 1 (returns $0)
>>> -> Index Only Scan using idx_cachekeys on cachekeys
>>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
>>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>> 'U-523596'::text))
>>> Heap Fetches: 0
>>> Planning Time: 0.221 ms
>>> Execution Time: 0.046 ms
>>>
>>
>> That looks pretty reasonable.
>>
>> 1. Now show what happens with the LIMIT clause.
>> 2. How many rows does it return?
>> 3. Do you keep the table regularly vacuumed and analyzed?
>>
>> Hey Ron
>
> 1. Now show what happens with the LIMIT clause.
> and result set of query and *Size of the table 287MB*
> exists
> --------
> t
> (1 row)
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030 rows=1
> loops=1)
> InitPlan 1 (returns $0)
> -> Index Only Scan using idx_cachekeys on cachekeys
> (cost=0.55..2.80 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
> 'U-523596'::text))
> Heap Fetches: 1
> Planning Time: 0.084 ms
> Execution Time: 0.043 ms
>
This might be due to caching. Run the query with LIMIT three times, and
then remove the LIMIT and run three times.
Honestly, though, the execution timings seem pretty good. What exactly is
the problem?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:48 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 48+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 03:48 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
>> wrote:
>>
>>> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
>>> maheshpostgres9@gmail.com> wrote:
>>> [snip]
>>>
>>>> Hi Adrian Klaver
>>>>
>>>> 1) Postgres version.
>>>> select version();
>>>> version
>>>>
>>>> ---------------------------------------------------------------------------------------------------------------
>>>> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
>>>> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>>>>
>>>> 2) Complete(including indexes) table schema.
>>>>
>>>> Table
>>>> "liveaggregations.cachekeys"
>>>> Column | Type | Collation | Nullable |
>>>> Default | Storage | Compression
>>>>
>>>> ---------------+------------------------+-----------+----------+---------+----------+------------
>>>> cachetype | character varying(255) | | |
>>>> | extended |
>>>> trsid | character varying(255) | | |
>>>> | extended |
>>>> brandid | character varying(255) | | |
>>>> | extended |
>>>> sportid | character varying(255) | | |
>>>> | extended |
>>>> competitionid | character varying(255) | | |
>>>> | extended |
>>>> eventid | character varying(255) | | |
>>>> | extended |
>>>> marketid | character varying(255) | | |
>>>> | extended |
>>>> selectionid | character varying(255) | | |
>>>> | extended |
>>>> keytype | character varying(255) | | |
>>>> | extended |
>>>> key | character varying(255) | | not null |
>>>> | extended |
>>>> Indexes:
>>>> "cachekeys_key_pk" PRIMARY KEY, btree (key)
>>>> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
>>>> competitionid, eventid, marketid)
>>>> "idx_marketid" btree (marketid)
>>>>
>>>> 3) Output of EXPLAIN ANALYZE of query.
>>>>
>>>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030
>>>> rows=1 loops=1)
>>>> InitPlan 1 (returns $0)
>>>> -> Index Only Scan using idx_cachekeys on cachekeys
>>>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
>>>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>>>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>>>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>>> 'U-523596'::text))
>>>> Heap Fetches: 0
>>>> Planning Time: 0.221 ms
>>>> Execution Time: 0.046 ms
>>>>
>>>
>>> That looks pretty reasonable.
>>>
>>> 1. Now show what happens with the LIMIT clause.
>>> 2. How many rows does it return?
>>> 3. Do you keep the table regularly vacuumed and analyzed?
>>>
>>> Hey Ron
>>
>> 1. Now show what happens with the LIMIT clause.
>> and result set of query and *Size of the table 287MB*
>> exists
>> --------
>> t
>> (1 row)
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030
>> rows=1 loops=1)
>> InitPlan 1 (returns $0)
>> -> Index Only Scan using idx_cachekeys on cachekeys
>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>> 'U-523596'::text))
>> Heap Fetches: 1
>> Planning Time: 0.084 ms
>> Execution Time: 0.043 ms
>>
>
> This might be due to caching. Run the query with LIMIT three times, and
> then remove the LIMIT and run three times.
>
> Honestly, though, the execution timings seem pretty good. What exactly is
> the problem?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hi Team and Andrian
LIMIT is not necessary to use in select here in this case
To return one row takes 43ms is not optimal
Regards,
Durga Mahesh
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:55 David G. Johnston <david.g.johnston@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 0 replies; 48+ messages in thread
From: David G. Johnston @ 2025-03-14 03:55 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
On Thursday, March 13, 2025, Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:
>
>
> ------------------------------------------------------------
>>>>> ------------------------------------------------------------
>>>>> ------------------------------------------------------------
>>>>> --------------------------------------
>>>>>
>>>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030
>>> rows=1 loops=1)
>>> InitPlan 1 (returns $0)
>>> -> Index Only Scan using idx_cachekeys on cachekeys
>>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
>>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>> 'U-523596'::text))
>>> Heap Fetches: 1
>>> Planning Time: 0.084 ms
>>> Execution Time: 0.043 ms
>>>
>>
>>
>> To return one row takes 43ms is not optimal
>>
>
You are off by a factor of 1000 in your claimed performance. It’s 0.043ms
David J.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:57 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 0 replies; 48+ messages in thread
From: Ron Johnson @ 2025-03-14 03:57 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
> On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
>> maheshpostgres9@gmail.com> wrote:
>>
>>> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
>>> wrote:
>>>
>>>> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
>>>> maheshpostgres9@gmail.com> wrote:
>>>> [snip]
>>>>
>>>>> Hi Adrian Klaver
>>>>>
>>>>> 1) Postgres version.
>>>>> select version();
>>>>> version
>>>>>
>>>>> ---------------------------------------------------------------------------------------------------------------
>>>>> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
>>>>> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>>>>>
>>>>> 2) Complete(including indexes) table schema.
>>>>>
>>>>> Table
>>>>> "liveaggregations.cachekeys"
>>>>> Column | Type | Collation | Nullable |
>>>>> Default | Storage | Compression
>>>>>
>>>>> ---------------+------------------------+-----------+----------+---------+----------+------------
>>>>> cachetype | character varying(255) | | |
>>>>> | extended |
>>>>> trsid | character varying(255) | | |
>>>>> | extended |
>>>>> brandid | character varying(255) | | |
>>>>> | extended |
>>>>> sportid | character varying(255) | | |
>>>>> | extended |
>>>>> competitionid | character varying(255) | | |
>>>>> | extended |
>>>>> eventid | character varying(255) | | |
>>>>> | extended |
>>>>> marketid | character varying(255) | | |
>>>>> | extended |
>>>>> selectionid | character varying(255) | | |
>>>>> | extended |
>>>>> keytype | character varying(255) | | |
>>>>> | extended |
>>>>> key | character varying(255) | | not null |
>>>>> | extended |
>>>>> Indexes:
>>>>> "cachekeys_key_pk" PRIMARY KEY, btree (key)
>>>>> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,
>>>>> competitionid, eventid, marketid)
>>>>> "idx_marketid" btree (marketid)
>>>>>
>>>>> 3) Output of EXPLAIN ANALYZE of query.
>>>>>
>>>>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030
>>>>> rows=1 loops=1)
>>>>> InitPlan 1 (returns $0)
>>>>> -> Index Only Scan using idx_cachekeys on cachekeys
>>>>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
>>>>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>>>>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>>>>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>>>> 'U-523596'::text))
>>>>> Heap Fetches: 0
>>>>> Planning Time: 0.221 ms
>>>>> Execution Time: 0.046 ms
>>>>>
>>>>
>>>> That looks pretty reasonable.
>>>>
>>>> 1. Now show what happens with the LIMIT clause.
>>>> 2. How many rows does it return?
>>>> 3. Do you keep the table regularly vacuumed and analyzed?
>>>>
>>>> Hey Ron
>>>
>>> 1. Now show what happens with the LIMIT clause.
>>> and result set of query and *Size of the table 287MB*
>>> exists
>>> --------
>>> t
>>> (1 row)
>>>
>>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030
>>> rows=1 loops=1)
>>> InitPlan 1 (returns $0)
>>> -> Index Only Scan using idx_cachekeys on cachekeys
>>> (cost=0.55..2.80 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
>>> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid =
>>> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid =
>>> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>> 'U-523596'::text))
>>> Heap Fetches: 1
>>> Planning Time: 0.084 ms
>>> Execution Time: 0.043 ms
>>>
>>
>> This might be due to caching. Run the query with LIMIT three times, and
>> then remove the LIMIT and run three times.
>>
>> Honestly, though, the execution timings seem pretty good. What exactly
>> is the problem?
>>
>
> Hi Team and Andrian
>
> LIMIT is not necessary to use in select here in this case
>
> To return one row takes 43ms is not optimal
>
What did it used to take?
Planning takes 2x as long as execution. What if you just run "SELECT Key
FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND
SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND MarketId = $7" and
change app so that "returns one or more rows means true"?
This is also a valid method:
SELECT COUNT(*) FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND
BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND
MarketId = $7
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query optimization
@ 2025-03-14 14:00 Greg Sabino Mullane <htamfids@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 0 replies; 48+ messages in thread
From: Greg Sabino Mullane @ 2025-03-14 14:00 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
On Thu, Mar 13, 2025 at 11:49 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
> To return one row takes 43ms is not optimal
>
It's actually much faster than that, but even 43ms is overall good. The
query is already pretty optimal, as it uses a single index only scan. There
are a few tricks left to make this potentially faster, but you may want to
look into using some sort of in-memory caching system if your application
is that sensitive to timings. Or find us a version in which the execution
time is not 1/20th of a millisecond and we can work on that one.
Also take a look at pg_stat_statements so you can see how fast the query is
on average.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 48+ messages in thread
end of thread, other threads:[~2025-03-14 14:00 UTC | newest]
Thread overview: 48+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-04 08:17 Query optimization Siva Kumar <tech@leatherlink.net>
2002-10-04 10:14 ` Richard Huxton <dev@archonet.com>
2002-10-04 12:10 ` Neil Conway <neilc@samurai.com>
2002-10-04 12:26 ` Siva Kumar <tech@leatherlink.net>
2002-10-04 12:42 ` Jochem van Dieten <jochemd@oli.tudelft.nl>
2002-10-04 12:42 ` Antonis Antoniou <a.antoniou@albourne.com>
2002-10-04 13:29 ` Richard Huxton <dev@archonet.com>
2002-10-07 11:28 ` Siva Kumar <tech@leatherlink.net>
2002-10-15 07:06 query optimization pginfo <pginfo@t1.unisoftbg.com>
2002-10-15 08:12 ` Re: query optimization Mario Weilguni <mweilguni@sime.com>
2002-10-15 13:03 ` Re: query optimization Gaetano Mendola <mendola@bigfoot.com>
2005-08-12 16:11 query optimization marcelo Cortez <jmdc_marcelo@yahoo.com.ar>
2005-08-12 16:28 ` Re: query optimization Richard Huxton <dev@archonet.com>
2005-08-13 18:28 ` Re: query optimization marcelo Cortez <jmdc_marcelo@yahoo.com.ar>
2005-09-30 22:24 Query optimization Sean Davis <sdavis2@mail.nih.gov>
2005-10-01 15:29 ` Jim C. Nasby <jnasby@pervasive.com>
2008-07-15 18:42 query optimization Kevin Duffy <KD@wrinvestments.com>
2008-07-17 15:56 query optimization Kevin Duffy <KD@wrinvestments.com>
2008-07-17 18:00 ` Re: query optimization Scott Marlowe <scott.marlowe@gmail.com>
2008-07-17 23:41 ` Re: query optimization Klint Gore <kgore4@une.edu.au>
2008-07-18 14:39 ` Re: query optimization Kevin Duffy <KD@wrinvestments.com>
2012-04-11 10:28 Query optimization nissyre@wp.pl
2012-04-13 13:07 ` Sergey Konoplev <gray.ru@gmail.com>
2014-10-29 01:03 Query optimization Jorge Arevalo <jorgearevalo@libregis.org>
2014-10-29 01:46 ` David G Johnston <david.g.johnston@gmail.com>
2014-10-29 01:54 ` David G Johnston <david.g.johnston@gmail.com>
2014-10-29 18:06 ` Jorge Arevalo <jorgearevalo@libregis.org>
2014-10-29 18:24 ` David Johnston <david.g.johnston@gmail.com>
2014-10-29 18:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 19:05 ` David Johnston <david.g.johnston@gmail.com>
2014-10-29 19:14 ` Jorge Arevalo <jorgearevalo@libregis.org>
2014-10-29 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 19:47 ` David Johnston <david.g.johnston@gmail.com>
2014-10-29 20:26 ` Jorge Arevalo <jorgearevalo@libregis.org>
2025-03-13 19:12 Query optimization Durgamahesh Manne <maheshpostgres9@gmail.com>
2025-03-13 19:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-03-14 02:18 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2025-03-14 02:34 ` Rob Sargent <robjsargent@gmail.com>
2025-03-14 02:39 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2025-03-14 02:49 ` Rob Sargent <robjsargent@gmail.com>
2025-03-14 02:49 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-14 03:27 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2025-03-14 03:40 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-14 03:48 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2025-03-14 03:55 ` David G. Johnston <david.g.johnston@gmail.com>
2025-03-14 03:57 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-14 14:00 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-03-13 19:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
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