Обсуждение: index scan

Поиск
Список
Период
Сортировка

index scan

От
Amir Sehan
Дата:
Hello,

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?

Thanks,
Best Regards.

Re: index scan

От
Josh Berkus
Дата:
Amir,

> 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.

This is to test your indexing code?

Then:

 SET enable_seqscan = false;


--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com