Thread: Q: will GROUP BY make use of an index to return tuples early?
Hi pgsql optimizer gurus, If I have a table CREATE TABLE Foo( id OID, time TIMESTAMP, value INTEGER ); CREATE INDEX Foo_id_idx ON Foo(id); and I have a query for SELECT id, MIN(foo.time) FROM Foo foo GROUP BY foo.id; can one tell the query executor to do an index scan on Foo_id_idx such as to be sure the Foo tuples are being considered ordered by foo.id in order to produce tuples before having worked through the whole table? If we don't use that assumption of the index order and would instead to a full table scan, the select could not return anything until the full table scan is completed. I am asking because if I wanted to stream the tuples of the first query into anothe system to do a distributed semijoin, I would like data to flow at all times while queries are still being executed. If the answer is yes, there is a way, then how about if we do this: CREATE INDEX Foo_id_time_idx ON Foo(id, time); now, considering the same query, it could be executed even faster, because we could do an index scan on Foo_id_time_idx and only need to consider the first data tuple of every Foo.id group (because the ordering of Foo_id_time_idx guarantees that the MIN(time) is in the first tuple. thank you, -Gunther -- Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant Professor Indiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org
Gunther Schadow <gunther@aurora.regenstrief.org> writes: > SELECT id, MIN(foo.time) > FROM Foo foo > GROUP BY foo.id; > can one tell the query executor to do an index scan on Foo_id_idx That is a considered plan type. For example, using the regression test database: regression=# set enable_seqscan TO 0; SET regression=# explain analyze select hundred,min(unique1) from tenk1 group by hundred; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=0.00..1121.89 rows=1000 width=8) (actual time=19.12..1619.71 rows=100 loops=1) -> Group (cost=0.00..1096.89 rows=10000width=8) (actual time=0.56..1484.14 rows=10000 loops=1) -> Index Scan using tenk1_hundred on tenk1 (cost=0.00..1071.89rows=10000 width=8) (actual time=0.53..1194.64 rows=10000 loops=1)Total runtime: 1621.69 msec (4 rows) regression=# set enable_seqscan TO 1; SET regression=# explain analyze select hundred,min(unique1) from tenk1 group by hundred; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------Aggregate (cost=997.39..1072.39 rows=1000 width=8) (actual time=1162.52..1677.46 rows=100 loops=1) -> Group (cost=997.39..1047.39rows=10000 width=8) (actual time=1157.33..1565.75 rows=10000 loops=1) -> Sort (cost=997.39..1022.39rows=10000 width=8) (actual time=1157.29..1298.10 rows=10000 loops=1) Sort Key: hundred -> Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=8) (actual time=0.16..277.69 rows=10000 loops=1)Totalruntime: 1695.83 msec (6 rows) The sort-based plan usually comes out ahead in estimated total cost, but if you were using a cursor to read it then I believe the indexscan plan would get chosen due to its much lower startup cost. > If the answer is yes, there is a way, then how about if we do this: > CREATE INDEX Foo_id_time_idx ON Foo(id, time); > now, considering the same query, it could be executed even faster, > because we could do an index scan on Foo_id_time_idx and only need > to consider the first data tuple of every Foo.id group (because > the ordering of Foo_id_time_idx guarantees that the MIN(time) is > in the first tuple. This won't happen because (a) the optimizer treats aggregates as black boxes; there is no notion that some aggregates have a connection to the sort ordering of some indexes; (b) the indexscan would still have to pass over the other time values in each group. regards, tom lane