Thread: index scan
Hello, pgsql-sql.
I have customize simple query with join two tables:
xxxx=# \d sites Таблица
"public.sites" Колонка | Тип | Модификаторы
---------+------------------------+-----------------------name | character varying(255) | not nullrem | text
|enabled | boolean | not null default true
Index: "pk_sites" primary key, btree (oid)
xxxx=# \d site_screens
-------------+------------------------+--------------id_site | oid | not nullscreen_name |
charactervarying(255) | not nullscreen_code | text |
Foreign keys: "$1" FOREIGN KEY (id_site) REFERENCES sites(oid)
xxxx=# explain select * from sites s join site_screens ss on s.oid = ss.id_site; QUERY
PLAN
---------------------------------------------------------------------------HashJoin (cost=...) Hash Cond:
("outer".id_site= "inner".oid) -> Seq Scan on site_screens ss (cost=...) -> Hash (cost=...) -> Seq Scan
onsites s (cost=...)
I want to Index Scan. What must I do?
--
Regards,Mihail Nasedkinmailto:m.nasedkin.perm@mail.ru
Mihail Nasedkin wrote:
> xxxx=# explain select * from sites s join site_screens ss on s.oid = ss.id_site; QUERY
PLAN ---------------------------------------------------------------------------
> Hash Join (cost=...)
> Hash Cond: ("outer".id_site = "inner".oid)
> -> Seq Scan on site_screens ss (cost=...)
> -> Hash (cost=...)
> -> Seq Scan on sites s (cost=...)
You've commented out the interesting bits (the costs/rows)
> I want to Index Scan. What must I do?
Why do you want an index scan? Do you have any evidence it will be
faster than a sequential scan?
-- Richard Huxton Archonet Ltd
Die, Richard.
Thank you for answer March, 21 2005 14:15:40:
RH> Mihail Nasedkin wrote:
>> xxxx=# explain select * from sites s join site_screens ss on
>> s.oid = ss.id_site; QUERY PLAN
>> ---------------------------------------------------------------------------
>> Hash Join (cost=...)
>> Hash Cond: ("outer".id_site = "inner".oid)
>> -> Seq Scan on site_screens ss (cost=...)
>> -> Hash (cost=...)
>> -> Seq Scan on sites s (cost=...)
RH> You've commented out the interesting bits (the costs/rows)
In that example main point - Seq Scan on site_screens
>> I want to Index Scan. What must I do?
RH> Why do you want an index scan? Do you have any evidence it will be
RH> faster than a sequential scan?
No, but I want to be ready for make Index scan queries in future. I
make first steps on the customize SQL.
Where I can read more about optimize the SQL-queries and about
differences between types of scan?
--
Regards,Mihail Nasedkinm.nasedkin.perm@mail.ru
Please CC the list as well as replying directly to me. I don't read this email address often. Mihail Nasedkin wrote: > > RH> Why do you want an index scan? Do you have any evidence it will be > RH> faster than a sequential scan? > > No, but I want to be ready for make Index scan queries in future. I > make first steps on the customize SQL. PostgreSQL uses statistics on what values are in what columns to decide how to plan a query. So - if you are asking for all rows from a table it probably won't use an index because it knows youwill have to read the whole table anyway. > Where I can read more about optimize the SQL-queries and about > differences between types of scan? Well, perhaps the best place to learn more is the performance mailing list. You can see plenty of real-world problems being discussed there. Two sections of the manual you should read are Chapter 13. Performance Tips Chapter 23. Monitoring Database Activity Understanding how to read EXPLAIN ANALYSE output and manage statistics are vital. Finally, details on configuration settings can be found at: http://www.powerpostgresql.com/PerfList http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- Richard Huxton Archonet Ltd