Re: [PERFORM] Improving Inner Join Performance
Did you originally post some problem queries? The settings look OK, though 1G of memory isn't very much now-a-days. On Mon, Jan 09, 2006 at 09:56:52AM +0200, Andy wrote: > shared_buffers = 10240 > effective_cache_size = 64000 > RAM on server: 1Gb. > > Andy. > > - Original Message - > > From: "Frank Wiles" <[EMAIL PROTECTED]> > To: "Andy" <[EMAIL PROTECTED]> > Sent: Friday, January 06, 2006 7:12 PM > Subject: Re: [PERFORM] Improving Inner Join Performance > > > > On Fri, 6 Jan 2006 09:59:30 +0200 > > "Andy" <[EMAIL PROTECTED]> wrote: > > > >> Yes I have indexes an all join fields. > >> The tables have around 30 columns each and around 100k rows. > >> The database is vacuumed every hour. > > > > What are you settings for: > > > > shared_buffers > > effective_cache_size > > > > And how much RAM do you have in the server? > > > > - > > Frank Wiles <[EMAIL PROTECTED]> > > http://www.wiles.org > > - > > > > > > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Improving Inner Join Performance
shared_buffers = 10240effective_cache_size = 64000RAM on server: 1Gb. Andy. - Original Message - From: "Frank Wiles" <[EMAIL PROTECTED]> To: "Andy" <[EMAIL PROTECTED]> Sent: Friday, January 06, 2006 7:12 PM Subject: Re: [PERFORM] Improving Inner Join Performance > On Fri, 6 Jan 2006 09:59:30 +0200> "Andy" <[EMAIL PROTECTED]> wrote:> >> Yes I have indexes an all join fields. >> The tables have around 30 columns each and around 100k rows. >> The database is vacuumed every hour. > > What are you settings for: > > shared_buffers > effective_cache_size> > And how much RAM do you have in the server? > > -> Frank Wiles <[EMAIL PROTECTED]>> http://www.wiles.org> -> > >
Re: [PERFORM] Improving Inner Join Performance
On 1/6/06, Andy <[EMAIL PROTECTED]> wrote: > At the moment: o.id_status>3 can have values only 4 and 6. The 6 is around > 90% from the whole table. This is why seq scan is made. > given this if you make id_status > 3 you will never use an index because you will be scanning 4 and 6 the only values in this field as you say, and even if there were any other value 6 is 90% of whole table, so an index for this will not be used... > Now, depending on the user input the query can have more where fields. For > example: > SELECT count(*) FROM orders o > INNER JOIN report r ON r.id_order=o.id > WHERE o.id_status > 3 AND r.id_zufriden=7 > here the planner can be more selective, and of course the query is faster... if you will be loading data load it all then make tests... but because your actual data the planner will always choose to scan the entire orders table for o.id_status > 3... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Improving Inner Join Performance
At the moment: o.id_status>3 can have values only 4 and 6. The 6 is around
90% from the whole table. This is why seq scan is made.
Now, depending on the user input the query can have more where fields. For
example:
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status > 3 AND r.id_zufriden=7
Aggregate (cost=7317.15..7317.15 rows=1 width=0) (actual
time=213.418..213.419 rows=1 loops=1)
-> Hash Join (cost=3139.00..7310.80 rows=2540 width=0) (actual
time=57.554..212.215 rows=1308 loops=1)
Hash Cond: ("outer".id = "inner".id_order)
-> Seq Scan on orders o (cost=0.00..3785.31 rows=72216 width=4)
(actual time=0.014..103.292 rows=72121 loops=1)
Filter: (id_status > 3)
-> Hash (cost=3132.51..3132.51 rows=2597 width=4) (actual
time=57.392..57.392 rows=0 loops=1)
-> Seq Scan on report r (cost=0.00..3132.51 rows=2597
width=4) (actual time=0.019..56.220 rows=1308 loops=1)
Filter: (id_zufriden = 7)
Total runtime: 213.514 ms
These examples can go on and on.
If I run this query
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status>3
under normal system load the average response time is between 1.3 > 2.5
seconds. Sometimes even more. If I run it rapidly a few times then it
respondes faster(that is normal I supose).
The ideea of this query is to count all the possible results that the user
can have. I use this to build pages of results.
Andy.
- Original Message -
From: "Pandurangan R S" <[EMAIL PROTECTED]>
To: "Andy" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, January 06, 2006 11:56 AM
Subject: Re: [PERFORM] Improving Inner Join Performance
If the users puts in some other search fields on the where then the query
runs faster but > in this format sometimes it takes a lot lot of
time(sometimes even 2,3 seconds).
Can you eloborate under what conditions which query is slower?
On 1/5/06, Andy <[EMAIL PROTECTED]> wrote:
Hi to all,
I have the following query:
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status>3
Explaing analyze:
Aggregate (cost=8941.82..8941.82 rows=1 width=0) (actual
time=1003.297..1003.298 rows=1 loops=1)
-> Hash Join (cost=3946.28..8881.72 rows=24041 width=0) (actual
time=211.985..951.545 rows=72121 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
(actual time=0.005..73.869 rows=72121 loops=1)
-> Hash (cost=3787.57..3787.57 rows=24682 width=4) (actual
time=211.855..211.855 rows=0 loops=1)
-> Seq Scan on orders o (cost=0.00..3787.57 rows=24682
width=4) (actual time=0.047..147.170 rows=72121 loops=1)
Filter: (id_status > 3)
Total runtime: 1003.671 ms
I could use it in the following format, because I have to the moment only
the 4,6 values for the id_status.
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status IN (4,6)
Explain analyze:
Aggregate (cost=5430.04..5430.04 rows=1 width=0) (actual
time=1472.877..1472.877 rows=1 loops=1)
-> Hash Join (cost=2108.22..5428.23 rows=720 width=0) (actual
time=342.080..1419.775 rows=72121 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
(actual time=0.036..106.217 rows=72121 loops=1)
-> Hash (cost=2106.37..2106.37 rows=739 width=4) (actual
time=342.011..342.011 rows=0 loops=1)
-> Index Scan using orders_id_status_idx,
orders_id_status_idx on orders o (cost=0.00..2106.37 rows=739 width=4)
(actual time=0.131..268.397 rows=72121 loops=1)
Index Cond: ((id_status = 4) OR (id_status = 6))
Total runtime: 1474.356 ms
How can I improve this query's performace?? The ideea is to count all the
values that I have in the database for the following conditions. If the
users puts in some other search fields on the where then the query runs
faster but in this format sometimes it takes a lot lot of time(sometimes
even 2,3 seconds).
Can this be tuned somehow???
Regards,
Andy.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Improving Inner Join Performance
> If the users puts in some other search fields on the where then the query
> runs faster but > in this format sometimes it takes a lot lot of
> time(sometimes even 2,3 seconds).
Can you eloborate under what conditions which query is slower?
On 1/5/06, Andy <[EMAIL PROTECTED]> wrote:
>
> Hi to all,
>
> I have the following query:
>
> SELECT count(*) FROM orders o
> INNER JOIN report r ON r.id_order=o.id
> WHERE o.id_status>3
>
> Explaing analyze:
> Aggregate (cost=8941.82..8941.82 rows=1 width=0) (actual
> time=1003.297..1003.298 rows=1 loops=1)
> -> Hash Join (cost=3946.28..8881.72 rows=24041 width=0) (actual
> time=211.985..951.545 rows=72121 loops=1)
> Hash Cond: ("outer".id_order = "inner".id)
> -> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
> (actual time=0.005..73.869 rows=72121 loops=1)
> -> Hash (cost=3787.57..3787.57 rows=24682 width=4) (actual
> time=211.855..211.855 rows=0 loops=1)
> -> Seq Scan on orders o (cost=0.00..3787.57 rows=24682
> width=4) (actual time=0.047..147.170 rows=72121 loops=1)
> Filter: (id_status > 3)
> Total runtime: 1003.671 ms
>
>
> I could use it in the following format, because I have to the moment only
> the 4,6 values for the id_status.
>
> SELECT count(*) FROM orders o
> INNER JOIN report r ON r.id_order=o.id
> WHERE o.id_status IN (4,6)
>
> Explain analyze:
> Aggregate (cost=5430.04..5430.04 rows=1 width=0) (actual
> time=1472.877..1472.877 rows=1 loops=1)
> -> Hash Join (cost=2108.22..5428.23 rows=720 width=0) (actual
> time=342.080..1419.775 rows=72121 loops=1)
> Hash Cond: ("outer".id_order = "inner".id)
> -> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
> (actual time=0.036..106.217 rows=72121 loops=1)
> -> Hash (cost=2106.37..2106.37 rows=739 width=4) (actual
> time=342.011..342.011 rows=0 loops=1)
> -> Index Scan using orders_id_status_idx,
> orders_id_status_idx on orders o (cost=0.00..2106.37 rows=739 width=4)
> (actual time=0.131..268.397 rows=72121 loops=1)
> Index Cond: ((id_status = 4) OR (id_status = 6))
> Total runtime: 1474.356 ms
>
> How can I improve this query's performace?? The ideea is to count all the
> values that I have in the database for the following conditions. If the
> users puts in some other search fields on the where then the query runs
> faster but in this format sometimes it takes a lot lot of time(sometimes
> even 2,3 seconds).
>
> Can this be tuned somehow???
>
> Regards,
> Andy.
>
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] Improving Inner Join Performance
Sorry, I had to be more specific. VACUUM ANALYZE is performed every hour. Regards, Andy. - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: "Andy" <[EMAIL PROTECTED]> Cc: Sent: Friday, January 06, 2006 11:45 AM Subject: Re: [PERFORM] Improving Inner Join Performance On Jan 6, 2006, at 18:21 , Andy wrote: Yes I have indexes an all join fields. The tables have around 30 columns each and around 100k rows. The database is vacuumed every hour. Just to chime in, VACUUM != VACUUM ANALYZE. ANALYZE is what updates database statistics and affects query planning. VACUUM alone does not do this. Do you have an index on report.id_order ? Try creating an index for it if not and run a vacuum analyze on the table to see if it gets rid of the sequence scan in the plan. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Improving Inner Join Performance
On Jan 6, 2006, at 18:21 , Andy wrote: Yes I have indexes an all join fields. The tables have around 30 columns each and around 100k rows. The database is vacuumed every hour. Just to chime in, VACUUM != VACUUM ANALYZE. ANALYZE is what updates database statistics and affects query planning. VACUUM alone does not do this. Do you have an index on report.id_order ? Try creating an index for it if not and run a vacuum analyze on the table to see if it gets rid of the sequence scan in the plan. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Improving Inner Join Performance
Yes I have indexes an all join fields. The tables have around 30 columns each and around 100k rows. The database is vacuumed every hour. Andy. - Original Message - From: "Frank Wiles" <[EMAIL PROTECTED]> To: "Andy" <[EMAIL PROTECTED]> Cc: Sent: Thursday, January 05, 2006 9:20 PM Subject: Re: [PERFORM] Improving Inner Join Performance On Thu, 5 Jan 2006 17:16:47 +0200 "Andy" <[EMAIL PROTECTED]> wrote: Hi to all, I have the following query: SELECT count(*) FROM orders o INNER JOIN report r ON r.id_order=o.id WHERE o.id_status>3 How can I improve this query's performace?? The ideea is to count all the values that I have in the database for the following conditions. If the users puts in some other search fields on the where then the query runs faster but in this format sometimes it takes a lot lot of time(sometimes even 2,3 seconds). Can this be tuned somehow??? Do you have an index on report.id_order ? Try creating an index for it if not and run a vacuum analyze on the table to see if it gets rid of the sequence scan in the plan. - Frank Wiles <[EMAIL PROTECTED]> http://www.wiles.org - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Improving Inner Join Performance
On Thu, 5 Jan 2006 17:16:47 +0200 "Andy" <[EMAIL PROTECTED]> wrote: > Hi to all, > > I have the following query: > > SELECT count(*) FROM orders o > INNER JOIN report r ON r.id_order=o.id > WHERE o.id_status>3 > How can I improve this query's performace?? The ideea is to count all > the values that I have in the database for the following conditions. > If the users puts in some other search fields on the where then the > query runs faster but in this format sometimes it takes a lot lot of > time(sometimes even 2,3 seconds). > > Can this be tuned somehow??? Do you have an index on report.id_order ? Try creating an index for it if not and run a vacuum analyze on the table to see if it gets rid of the sequence scan in the plan. - Frank Wiles <[EMAIL PROTECTED]> http://www.wiles.org - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Improving Inner Join Performance
Hi to all,
I have the following query:
SELECT count(*) FROM orders
o INNER JOIN report r ON
r.id_order=o.id WHERE
o.id_status>3
Explaing analyze:
Aggregate (cost=8941.82..8941.82 rows=1
width=0) (actual time=1003.297..1003.298 rows=1 loops=1) ->
Hash Join (cost=3946.28..8881.72 rows=24041 width=0) (actual
time=211.985..951.545 rows=72121
loops=1) Hash Cond:
("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
(actual time=0.005..73.869 rows=72121
loops=1) -> Hash
(cost=3787.57..3787.57 rows=24682 width=4) (actual time=211.855..211.855 rows=0
loops=1)
-> Seq Scan on orders o (cost=0.00..3787.57 rows=24682 width=4)
(actual time=0.047..147.170 rows=72121
loops=1)
Filter: (id_status > 3)Total runtime: 1003.671 ms
I could use it in the following format, because I
have to the moment only the 4,6 values for the id_status.
SELECT count(*) FROM orders
o INNER JOIN report r ON
r.id_order=o.id WHERE o.id_status IN
(4,6)
Explain analyze:Aggregate
(cost=5430.04..5430.04 rows=1 width=0) (actual time=1472.877..1472.877 rows=1
loops=1) -> Hash Join (cost=2108.22..5428.23 rows=720
width=0) (actual time=342.080..1419.775 rows=72121
loops=1) Hash Cond:
("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4)
(actual time=0.036..106.217 rows=72121
loops=1) -> Hash
(cost=2106.37..2106.37 rows=739 width=4) (actual time=342.011..342.011 rows=0
loops=1)
-> Index Scan using orders_id_status_idx, orders_id_status_idx on
orders o (cost=0.00..2106.37 rows=739 width=4) (actual time=0.131..268.397
rows=72121
loops=1)
Index Cond: ((id_status = 4) OR (id_status = 6))Total runtime: 1474.356
ms
How can I improve this query's performace?? The
ideea is to count all the values that I have in the database for the following
conditions. If the users puts in some other search fields on the where then the
query runs faster but in this format sometimes it takes a lot lot of
time(sometimes even 2,3 seconds).
Can this be tuned somehow???
Regards,
Andy.
