Sreedhar,
You need to run VACUUM on a regular basis in order to
have up-to-date database statistics for the PostgreSQL
planner.
The EXPLAIN command just has an estimation of how many
rows will get processed and what the best way should be
to execute the query. Without good table statistics
this estimation can be completely wrong.
You say that your tempaccountid table has only 10 rows.
However, EXPLAIN shows the default of 1000 rows.
You say that the table tblPermissions has about 70,000
rows. But before indexing PostgreSQL does not know
that and again assumed the default.
By indexing the tblPermissions table you updated its
statistics and therefore the second EXPLAIN looks
different.
Regards,
Nikolaus Dilger
"shreedhar" wrote:
>
> pmdummy=# explain SELECT projectid FROM tblPermissions
> pmdummy-# INNER JOIN tempaccountid ON
> tempaccountid.accid = tblPermissions.
> countid
> pmdummy-# WHERE tblPermissions.topid = 3915;
>
> 1. tempaccountid (accid integer) no indexe or no
> primary key
> Number of Records 10
> 2. tblPermissions (accountid integer, raccountid
> integer, topid integer)
> primary key(accountid, raccountid)
> Number of Records appoximately 70,0000
>
> Before indexing on tblpermissions (topid) i got query
> plan as
>
>
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=22.51..47.83 rows=25 width=12)
> -> Seq Scan on tempaccountid (cost=0.00..20.00
> rows=1000 width=4)
> -> Hash (cost=22.50..22.50 rows=5 width=8)
> -> Seq Scan on tblpermissions
> (cost=0.00..22.50 rows=5 width=8)
>
> EXPLAIN
>
> After indexing on tblpermissions (topid) i got query
> plan as
>
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=1345.76..1375.89 rows=2126 width=12)
> -> Sort (cost=1275.93..1275.93 rows=425 width=8)
> -> Index Scan using idx_tblpermissions_topid
> on tblpermissions
> (cost=0
> .00..1257.37 rows=425 width=8)
> -> Sort (cost=69.83..69.83 rows=1000 width=4)
> -> Seq Scan on tempaccountid
> (cost=0.00..20.00 rows=1000 width=4)
>
> EXPLAIN
>
> But I could not under stand this statistics. Can any
> body explain above.
>
> Thanks to all seniors and gurus,
>
> Sreedhar
>
>
> "Faith, faith, faith in ourselves, faith, faith in
God,
> this is the secret
> of greatness.
> If you have faith in all the three hundred and thirty
> millions of your
> mythological gods,
> and in all the gods which foreigners have now and
again
> introduced into your
> midst,
> and still have no faith in yourselves, there is no
> salvation for you. "
> (III. 190)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> <a
href="http://mail.dilger.cc/jump/http://www.postgresql.org/users-lounge/docs/faq.html">http://www.postgresql.org/users-lounge/docs/faq.html</a>