pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
"Group by" and "index".
3+ messages / 2 participants
[nested] [flat]

* "Group by" and "index".
@ 2000-01-30 21:01  Vlad Marchenko <vlad@infonet.com.ua>
  0 siblings, 0 replies; 3+ messages in thread

From: Vlad Marchenko @ 2000-01-30 21:01 UTC (permalink / raw)
  To: pgsql-sql

Hi,

PostgreSQL 6.5.3.

I've created the following index:

>create index test_index on test (filed1);
>vacuum;

And then:

>explain select field1 from test group by field1;
NOTICE:  QUERY PLAN:

Group  (cost=2510.40 rows=58709 width=12)
  ->  Sort  (cost=2510.40 rows=58709 width=12)
        ->  Seq Scan on test  (cost=2510.40 rows=58709 width=12)

It don't use this index :-(. I've tried to create two types of index:
b-tree and hash - both doesn't work until "group" is present. If I type

> explain select field1 from test where field1='tra-ta-ta';

NOTICE:  QUERY PLAN:

Index Scan using test_index on test (cost=2.05 rows=2 width=12)


It work this way.


Any ideas?

_____________
Vlad Marchenko




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* "Group by" and "index".
@ 2000-01-30 21:23  Vlad Marchenko <vlad@infonet.com.ua>
  0 siblings, 1 reply; 3+ messages in thread

From: Vlad Marchenko @ 2000-01-30 21:23 UTC (permalink / raw)
  To: pgsql-sql

Hi,

PostgreSQL 6.5.3.

I've created the following index:

>create index test_index on test (filed1);
>vacuum;

And then:

>explain select field1 from test group by field1;
NOTICE:  QUERY PLAN:

Group  (cost=2510.40 rows=58709 width=12)
  ->  Sort  (cost=2510.40 rows=58709 width=12)
        ->  Seq Scan on test  (cost=2510.40 rows=58709 width=12)

It don't use this index :-(. I've tried to create two types of index:
b-tree and hash - both doesn't work until "group" is present. If I type

> explain select field1 from test where field1='tra-ta-ta';

NOTICE:  QUERY PLAN:

Index Scan using test_index on test (cost=2.05 rows=2 width=12)


It work this way.


Any ideas?

_____________
Vlad Marchenko





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] "Group by" and "index".
@ 2000-01-30 22:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Vlad Marchenko <vlad@infonet.com.ua>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-01-30 22:29 UTC (permalink / raw)
  To: Vlad Marchenko <vlad@infonet.com.ua>; +Cc: pgsql-sql

"Vlad Marchenko" <vlad@infonet.com.ua> writes:
>> explain select field1 from test group by field1;
> It don't use this index :-(.

That's a deficiency of the 6.5 planner; it won't consider an indexscan
unless there's a WHERE clause that matches the index.  This is fixed for
7.0.

However, the 6.5 planner is not necessarily doing the wrong thing here!
Replacing the explicit sort with an index scan could easily produce a
slower query.  Index scans are good for pulling out a few items,
but when you are going to end up fetching the whole table, they are
pretty slow because of the nonsequential disk accesses they cause.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-01-30 22:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-30 21:01 "Group by" and "index". Vlad Marchenko <vlad@infonet.com.ua>
2000-01-30 21:23 "Group by" and "index". Vlad Marchenko <vlad@infonet.com.ua>
2000-01-30 22:29 ` Tom Lane <tgl@sss.pgh.pa.us>

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