pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Index scan
6+ messages / 4 participants
[nested] [flat]
* Index scan
@ 2001-02-01 17:02 Najm Hashmi <najm@mondo-live.com>
2001-01-31 17:42 ` Re: Index scan Mark Volpe <volpe.mark@epamail.epa.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Najm Hashmi @ 2001-02-01 17:02 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I am unable to understand why my inidcies are not used in the query.
I have following indices:
index on categories.root
index on media.category
unique index on categories.id
Here is my query :
mondo=# explain select m.id
form media m, categories c
where c.id=m.category and c.root like 'B%';
NOTICE: QUERY PLAN:
Hash Join (cost=22.55..116.80 rows=11 width=28)
-> Seq Scan on media m (cost=0.00..60.40 rows=840 width=16)
-> Hash (cost=22.50..22.50 rows=20 width=12)
-> Index Scan using cat_id_root on categories c
(cost=0.00..22.50 rows=20 width=12)
EXPLAIN
I simply dont understand why it uses seq scan on media.
Thanks all for your help.
-Najm
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index scan
2001-02-01 17:02 Index scan Najm Hashmi <najm@mondo-live.com>
@ 2001-01-31 17:42 ` Mark Volpe <volpe.mark@epamail.epa.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Volpe @ 2001-01-31 17:42 UTC (permalink / raw)
To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql
Since you are selecting all the rows of media, there is no reason to use the
index to do this as it would just slow things down.
Mark
Najm Hashmi wrote:
>
> Hi all,
> I am unable to understand why my inidcies are not used in the query.
> I have following indices:
> index on categories.root
> index on media.category
> unique index on categories.id
> Here is my query :
> mondo=# explain select m.id
> form media m, categories c
> where c.id=m.category and c.root like 'B%';
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=22.55..116.80 rows=11 width=28)
> -> Seq Scan on media m (cost=0.00..60.40 rows=840 width=16)
> -> Hash (cost=22.50..22.50 rows=20 width=12)
> -> Index Scan using cat_id_root on categories c
> (cost=0.00..22.50 rows=20 width=12)
> EXPLAIN
>
> I simply dont understand why it uses seq scan on media.
> Thanks all for your help.
> -Najm
^ permalink raw reply [nested|flat] 6+ messages in thread
* index scan
@ 2005-03-21 08:26 Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 09:15 ` Re: index scan Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mihail Nasedkin @ 2005-03-21 08:26 UTC (permalink / raw)
To: pgsql-sql
Hello, pgsql-sql.
I have customize simple query with join two tables:
xxxx=# \d sites Таблица "public.sites" Колонка | Тип | Модификаторы
---------+------------------------+-----------------------
name | character varying(255) | not null
rem | text |
enabled | boolean | not null default true
Index:
"pk_sites" primary key, btree (oid)
xxxx=# \d site_screens
-------------+------------------------+--------------
id_site | oid | not null
screen_name | character varying(255) | not null
screen_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 ---------------------------------------------------------------------------
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=...)
I want to Index Scan. What must I do?
--
Regards,
Mihail Nasedkin
mailto:m.nasedkin.perm@mail.ru
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index scan
2005-03-21 08:26 index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
@ 2005-03-21 09:15 ` Richard Huxton <dev@archonet.com>
2005-03-21 10:05 ` Re: index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 11:35 ` Re: index scan Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Huxton @ 2005-03-21 09:15 UTC (permalink / raw)
To: Mihail Nasedkin <m.nasedkin.perm@mail.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index scan
2005-03-21 08:26 index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 09:15 ` Re: index scan Richard Huxton <dev@archonet.com>
@ 2005-03-21 10:05 ` Mihail Nasedkin <m.nasedkin.perm@mail.ru>
1 sibling, 0 replies; 6+ messages in thread
From: Mihail Nasedkin @ 2005-03-21 10:05 UTC (permalink / raw)
To: pgsql-sql
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 Nasedkin
m.nasedkin.perm@mail.ru
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index scan
2005-03-21 08:26 index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 09:15 ` Re: index scan Richard Huxton <dev@archonet.com>
@ 2005-03-21 11:35 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2005-03-21 11:35 UTC (permalink / raw)
To: Mihail Nasedkin <m.nasedkin.perm@mail.ru>; pgsql-sql
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 you will 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-03-21 11:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-01 17:02 Index scan Najm Hashmi <najm@mondo-live.com>
2001-01-31 17:42 ` Mark Volpe <volpe.mark@epamail.epa.gov>
2005-03-21 08:26 index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 09:15 ` Re: index scan Richard Huxton <dev@archonet.com>
2005-03-21 10:05 ` Re: index scan Mihail Nasedkin <m.nasedkin.perm@mail.ru>
2005-03-21 11:35 ` Re: index scan Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox