Обсуждение: query hangs
Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: ("outer".emailid = "inner".activityid)
-> Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: ("outer".emailid = "inner".mailid)
-> Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
-> Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
-> Seq Scan on vtiger_vantage_email_track (cost=0.00..1369.52 rows=88852 width=12)
-> Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
----------------------------+-------------+----------
vtiger_emaildetails | 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: ("outer".emailid = "inner".activityid)
-> Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: ("outer".emailid = "inner".mailid)
-> Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
-> Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
-> Seq Scan on vtiger_vantage_email_track (cost=0.00..1369.52 rows=88852 width=12)
-> Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
----------------------------+-------------+----------
vtiger_emaildetails | 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
2010/6/10 AI Rumman <rummandba@gmail.com>
Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: ("outer".emailid = "inner".activityid)
-> Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: ("outer".emailid = "inner".mailid)
-> Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
-> Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
-> Seq Scan on vtiger_vantage_email_track (cost=0.00..1369.52 rows=88852 width=12)
-> Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
----------------------------+-------------+----------
vtiger_emaildetails | 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
Could you define what you mean by 'hangs'? Does it work or not?
Check table pg_locks for locking issues, maybe the query is just slow but not hangs.
Notice that the query just returns 2M rows, that can be quite huge number due to your database structure, data amount and current server configuration.
regards
Szymon Guz
I found only AccessShareLock in pg_locks during the query.
And the query does not return data though I have been waiting for 10 mins.
Do you have any idea ?
And the query does not return data though I have been waiting for 10 mins.
Do you have any idea ?
On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz <mabewlun@gmail.com> wrote:
2010/6/10 AI Rumman <rummandba@gmail.com>Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: ("outer".emailid = "inner".activityid)
-> Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: ("outer".emailid = "inner".mailid)
-> Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
-> Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
-> Seq Scan on vtiger_vantage_email_track (cost=0.00..1369.52 rows=88852 width=12)
-> Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
----------------------------+-------------+----------
vtiger_emaildetails | 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)Could you define what you mean by 'hangs'? Does it work or not?Check table pg_locks for locking issues, maybe the query is just slow but not hangs.Notice that the query just returns 2M rows, that can be quite huge number due to your database structure, data amount and current server configuration.regardsSzymon Guz
2010/6/10 AI Rumman <rummandba@gmail.com>
I found only AccessShareLock in pg_locks during the query.
And the query does not return data though I have been waiting for 10 mins.
Do you have any idea ?On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz <mabewlun@gmail.com> wrote:2010/6/10 AI Rumman <rummandba@gmail.com>Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: ("outer".emailid = "inner".activityid)
-> Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: ("outer".emailid = "inner".mailid)
-> Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
-> Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
-> Seq Scan on vtiger_vantage_email_track (cost=0.00..1369.52 rows=88852 width=12)
-> Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
----------------------------+-------------+----------
vtiger_emaildetails | 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)Could you define what you mean by 'hangs'? Does it work or not?Check table pg_locks for locking issues, maybe the query is just slow but not hangs.Notice that the query just returns 2M rows, that can be quite huge number due to your database structure, data amount and current server configuration.regardsSzymon Guz
1. Make vacuum analyze on used tables.
2. Check how long it would take if you limit the number of returned rows just to 100
3. Do you have indexes on used columns?
regards
Szymon Guz
AI Rumman wrote: >> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506) > And the query does not return data though I have been waiting for > 10 mins. > > Do you have any idea ? Unless you use a cursor, PostgreSQL interfaces typically don't show any response on the client side until all rows have been received and cached on the client side. That's estimated to be over 2.6 million rows in this case. That can take a while. You might want to use a cursor.... -Kevin
Could you please give me the link for cursor- How to use it?
On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)> And the query does not return data though I have been waiting forUnless you use a cursor, PostgreSQL interfaces typically don't show
> 10 mins.
>
> Do you have any idea ?
any response on the client side until all rows have been received and
cached on the client side. That's estimated to be over 2.6 million
rows in this case. That can take a while.
You might want to use a cursor....
-Kevin
On 10 June 2010 18:05, AI Rumman <rummandba@gmail.com> wrote:
If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows .
E.g. \set FETCH_COUNT 1
will start fetching and displaying each row one by one.
Could you please give me the link for cursor- How to use it?On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)> And the query does not return data though I have been waiting forUnless you use a cursor, PostgreSQL interfaces typically don't show
> 10 mins.
>
> Do you have any idea ?
any response on the client side until all rows have been received and
cached on the client side. That's estimated to be over 2.6 million
rows in this case. That can take a while.
You might want to use a cursor....
If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows .
E.g. \set FETCH_COUNT 1
will start fetching and displaying each row one by one.
-Kevin
I am using Postgresql 8.1 and did not find FETCH_COUNT
On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <amit.khandekar@enterprisedb.com> wrote:
On 10 June 2010 18:05, AI Rumman <rummandba@gmail.com> wrote:Could you please give me the link for cursor- How to use it?On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)> And the query does not return data though I have been waiting forUnless you use a cursor, PostgreSQL interfaces typically don't show
> 10 mins.
>
> Do you have any idea ?
any response on the client side until all rows have been received and
cached on the client side. That's estimated to be over 2.6 million
rows in this case. That can take a while.
You might want to use a cursor....
If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows .
E.g. \set FETCH_COUNT 1
will start fetching and displaying each row one by one.-Kevin
On 10 June 2010 18:47, AI Rumman <rummandba@gmail.com> wrote:
Oh ok. Looks like FETCH_COUNT was introduced in 8.2
I am using Postgresql 8.1 and did not find FETCH_COUNT
Oh ok. Looks like FETCH_COUNT was introduced in 8.2
On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <amit.khandekar@enterprisedb.com> wrote:On 10 June 2010 18:05, AI Rumman <rummandba@gmail.com> wrote:Could you please give me the link for cursor- How to use it?On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)> And the query does not return data though I have been waiting forUnless you use a cursor, PostgreSQL interfaces typically don't show
> 10 mins.
>
> Do you have any idea ?
any response on the client side until all rows have been received and
cached on the client side. That's estimated to be over 2.6 million
rows in this case. That can take a while.
You might want to use a cursor....
If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows .
E.g. \set FETCH_COUNT 1
will start fetching and displaying each row one by one.-Kevin
Any more idea, please.
Is table partition a good solution for query optimization?
Is table partition a good solution for query optimization?
On Fri, Jun 11, 2010 at 11:09 AM, Amit Khandekar <amit.khandekar@enterprisedb.com> wrote:
On 10 June 2010 18:47, AI Rumman <rummandba@gmail.com> wrote:I am using Postgresql 8.1 and did not find FETCH_COUNT
Oh ok. Looks like FETCH_COUNT was introduced in 8.2On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <amit.khandekar@enterprisedb.com> wrote:On 10 June 2010 18:05, AI Rumman <rummandba@gmail.com> wrote:Could you please give me the link for cursor- How to use it?On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)> And the query does not return data though I have been waiting forUnless you use a cursor, PostgreSQL interfaces typically don't show
> 10 mins.
>
> Do you have any idea ?
any response on the client side until all rows have been received and
cached on the client side. That's estimated to be over 2.6 million
rows in this case. That can take a while.
You might want to use a cursor....
If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows .
E.g. \set FETCH_COUNT 1
will start fetching and displaying each row one by one.-Kevin
AI Rumman <rummandba@gmail.com> wrote: > [It takes a long time to return 2.6 million rows.] > Any more idea, please. I don't recall you telling us exactly what the environment and connection type is in which you're trying to return this large result set. Any specific suggestions would depend on that information. I do wonder why you are returning 2.6 million rows. A result set that large is rarely useful directly (except during data conversion or loading of some sort). Is there any filtering or aggregation happening on the client side with the received rows? If so, my first suggestion would be to make that part of the query, rather than part of the client code. > Is table partition a good solution for query optimization? Table partitioning is useful in some cases, but you haven't told us anything yet to indicate that it would help here. -Kevin
On Thu, Jun 10, 2010 at 5:36 AM, AI Rumman <rummandba@gmail.com> wrote: > I found only AccessShareLock in pg_locks during the query. > And the query does not return data though I have been waiting for 10 mins. > > Do you have any idea ? I have queries that run for hours. As long as it's using CPU / IO (use top in unix, whatever in windows to see) it's not hung, it's just taking longer than you expected. Those are not the same thing at all. Seeing as how you're joining three tables with millions of rows with no where clause, it's gonna take some to complete. Go grab a sandwich, etc, come back when it's done.