Re: creating of temporary table takes very long

Поиск
Список
Период
Сортировка
От Sriram Dandapani
Тема Re: creating of temporary table takes very long
Дата
Msg-id 6992E470F12A444BB787B5C937B9D4DF0406A654@ca-mail1.cis.local
обсуждение исходный текст
Ответ на creating of temporary table takes very long  ("Sriram Dandapani" <sdandapani@counterpane.com>)
Ответы Re: creating of temporary table takes very long  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Got an explain analyze output..Here it is


"Seq Scan on c_chkpfw_hr_tr a  (cost=0.00..225975659.89 rows=11000
width=136) (actual time=2.345..648070.474 rows=22001 loops=1)"
"  Filter: (subplan)"
"  SubPlan"
"    ->  Bitmap Heap Scan on chkpfw_tr_hr_dimension b
(cost=1474.64..10271.13 rows=1 width=0) (actual time=29.439..29.439
rows=1 loops=22001)"
"          Recheck Cond: (($0 = firstoccurrence) AND ($1 = sentryid_id)
AND ($2 = node_id))"
"          Filter: (($3 = customerid_id) AND (COALESCE($4, 0) =
COALESCE(interface_id, 0)) AND (COALESCE($5, 0) = COALESCE(source_id,
0)) AND (COALESCE($6, 0) = COALESCE(destination_id, 0)) AND
(COALESCE($7, 0) = COALESCE(sourceport_id, 0)) AND (COALESCE($8 (..)"
"          ->  Bitmap Index Scan on chkpfw_tr_hr_idx1
(cost=0.00..1474.64 rows=38663 width=0) (actual time=12.144..12.144
rows=33026 loops=22001)"
"                Index Cond: (($0 = firstoccurrence) AND ($1 =
sentryid_id) AND ($2 = node_id))"
"Total runtime: 648097.800 ms"

Regards

Sriram

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, April 17, 2006 12:29 PM
To: Sriram Dandapani
Cc: Pgsql-Performance (E-mail)
Subject: Re: [PERFORM] creating of temporary table takes very long

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> [ query snipped ]
> This takes forever (I have to cancel the statement each time)

How long did you wait?

> c_chkpfw_hr_tr has same indexes as chkpfw_tr_hr_dimension

Which would be what exactly?  What does EXPLAIN show for that SELECT?
(I won't make you post EXPLAIN ANALYZE, if you haven't got the patience
to let it finish, but you should at least provide EXPLAIN results.)

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Sriram Dandapani"
Дата:
Сообщение: Re: creating of temporary table takes very long
Следующее
От: Luckys
Дата:
Сообщение: Re: slow cursor