Skip site navigation (1) Skip section navigation (2)

index scan

From: Amir Sehan <ean_ibnisaid(at)yahoo(dot)com>
To: "pgsql-students(at)postgresql(dot)org" <pgsql-students(at)postgresql(dot)org>
Subject: index scan
Date: 2012-06-21 06:56:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-students

I have problem with EXPLAIN ANALYZE QUERY.
I want to change a plan node, from seq scan to index scan.
But i don't know the methode for it.

"HashAggregate  (cost=33.47..33.60 rows=13 width=1189) (actual time=156.499..156.573 rows=14 loops=1)"
"  ->  Append  (cost=1.31..31.75 rows=13 width=1189) (actual time=147.598..156.009 rows=14 loops=1)"
"        ->  Hash Join  (cost=1.31..24.24 rows=12 width=1192) (actual time=147.589..154.673 rows=12 loops=1)"
"              Hash Cond: (d.c_doctype_id = i.c_doctype_id)"
"              ->  Seq Scan on c_doctype d  (cost=0.00..4.01 rows=101 width=12) (actual time=0.195..0.986 rows=101 loops=1)"
"              ->  Hash  (cost=1.16..1.16 rows=12 width=1188) (actual time=0.366..0.366 rows=12 loops=1)"
"                    ->  Seq Scan on c_invoice i  (cost=0.00..1.16 rows=12 width=1188) (actual time=0.141..0.230 rows=12 loops=1)"
"                          Filter: (ispayschedulevalid <> 'Y'::bpchar)"
"        ->  Subquery Scan "*SELECT* 2"  (cost=1.18..7.39 rows=1 width=1149) (actual time=0.972..1.239 rows=2 loops=1)"
"              ->  Nested Loop  (cost=1.18..7.38 rows=1 width=1149) (actual time=0.940..1.189 rows=2 loops=1)"
"                    Join Filter: (i.c_invoice_id = ips.c_invoice_id)"
"                    ->  Hash Join  (cost=1.18..5.57 rows=1 width=1096) (actual time=0.630..0.778 rows=1 loops=1)"
"                          Hash Cond: (d.c_doctype_id = i.c_doctype_id)"
"                          ->  Seq Scan on c_doctype d  (cost=0.00..4.01 rows=101 width=12) (actual time=0.012..0.283 rows=101 loops=1)"
"                          ->  Hash  (cost=1.16..1.16 rows=1 width=1092) (actual time=0.081..0.081 rows=1 loops=1)"
"                                ->  Seq Scan on c_invoice i  (cost=0.00..1.16 rows=1 width=1092) (actual time=0.041..0.060 rows=1 loops=1)"
"                                      Filter: (ispayschedulevalid = 'Y'::bpchar)"
"                    ->  Seq Scan on c_invoicepayschedule ips  (cost=0.00..1.02 rows=1 width=66) (actual time=0.131..0.142 rows=2 loops=1)"
"                          Filter: (ips.isvalid = 'Y'::bpchar)"

Above is my query. The question is how to change seq scan to index scan to purpose optimize query. i.e seq scan on c_doctype_id?

Best Regards.


pgsql-students by date

Next:From: Josh BerkusDate: 2012-06-21 17:03:02
Subject: Re: index scan
Previous:From: Josh BerkusDate: 2012-05-23 17:11:38
Subject: Re: Negative card balance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group