<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1251"
http-equiv="Content-Type">
<title></title>
</head>
<body alink="#ee0000" bgcolor="#ffffff" link="#0000ee" text="#000000"
vlink="#551a8b">
<meta content="text/html;charset=windows-1251" http-equiv="Content-Type">
<title></title>
<meta content="text/html;charset=windows-1251" http-equiv="Content-Type">
<title></title>
Hi,<br>
<br>
The index doesn't cost you so much, seq SEQ Scan actully does:<br>
<font color="#ff0000"> Seq Scan on isbns_a_descubrir
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)</font><br>
<br>
This seq scan is called once for every row of librosdisponibilidadtemp
which passes the WHERE condition.<br>
So Here "<font color="#ff0000">Index Scan using
librosdisponibilidadtemp_idx_proceso on librosdisponibilidadtemp
(cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)"<font color="#000000"> it
says how much it will cost you to calculate the upper seq scan and the
seq scan on <font color="#ff0000">(</font></font>Seq Scan on
raizpaises) <font color="#000000">and the index scan on </font></font>libros.<br>
<br>
I suggest you to create index on table <b>isbns_a_descubrir </b>over
column isbn. This will hurry the query.<br>
And use join instead of IN for table raizpaises. This should also save
some time.<br>
<br>
Regards,<br>
Kaloyan Iliev<br>
<br>
<blockquote
cite="midbd8b58a40702270528i7c395029v4a8bf3eb442bc8c9(at)mail(dot)gmail(dot)com"
type="cite">ave this query:<br>
<br>
SELECT DISTINCT isbn, CURRENT_TIMESTAMP, 1<br>
FROM librosdisponibilidadtemp<br>
WHERE proceso = ai_proceso<br>
AND gen_isbn_pais(isbn) IN (SELECT pais FROM raizpaises)
<br>
AND NOT EXISTS<br>
( SELECT isbn<br>
FROM libros<br>
WHERE isbn = librosdisponibilidadtemp.isbn)<br>
AND NOT EXISTS<br>
( SELECT isbn <br>
FROM isbns_a_descubrir<br>
WHERE isbn = librosdisponibilidadtemp.isbn);<br>
<br>
and the plan execution is<br>
Unique (cost=133558107.45..133558128.13 rows=414 width=21) (actual
time=
790552.899..790553.098 rows=9 loops=1)<br>
-> Sort (cost=133558107.45..133558112.62 rows=2068 width=21)
(actual time=790552.882..790552.944 rows=9 loops=1)<br>
Sort Key: isbn, now(), 1<br>
-> Index Scan using librosdisponibilidadtemp_idx_proceso on
librosdisponibilidadtemp (cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)<br>
Index Cond: (proceso = 28465)<br>
Filter: ((hashed subplan) AND (NOT (subplan)) AND (NOT
(subplan)))<br>
SubPlan<br>
-> Seq Scan on isbns_a_descubrir
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)<br>
Filter: ((isbn)::bpchar = $1)<br>
-> Index Scan using "libros_idx_ISBN" on libros
(cost=0.00..5.95 rows=1 width=21) (actual time=12.938..12.938 rows=1
loops=50512)<br>
Index Cond: (isbn = $1)<br>
-> Seq Scan on raizpaises (cost=
0.00..1.01 rows=1 width=10) (actual time=0.764..0.871 rows=1 loops=1)<br>
Total runtime: 790553.561 ms<br>
<br>
The libros table has 1200000 regs.<br>
The isbns_a_descubrir table has 300000 regs.<br>
The librosdisponibilidadtemp table has 50000 regs. <br>
<br>
does anybody can explain me, why using index
ibrosdisponibilidadtemp_idx_proceso is so slow and the others
conditions are good enough<br>
Thanks everybody<br>
<br>
<br>
</blockquote>
<br>
</body>
</html>