pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not used on group by
6+ messages / 3 participants
[nested] [flat]
* Index not used on group by
@ 2005-09-27 09:14 Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrey Repko @ 2005-09-27 09:14 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I have table ma_data, that contain above 300000 rows.
This table has primary key id, and field alias_id.
I create index (btree)on this field.
Set statistic:
ALTER TABLE "public"."ma_data"
ALTER COLUMN "alias_id" SET STATISTICS 998;
So, when I do something like
SELECT alias_id FROM ma_data GROUP BY alias_id
and have (with seq_scan off):
Group (cost=0.00..1140280.63 rows=32 width=4) (actual time=0.159..2640.090 rows=32 loops=1)
-> Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57 rows=301624 width=4) (actual time=0.120..1471.128 rows=301624 loops=1)
Total runtime: 2640.407 ms
(3 rows)
As I understand there are some problems with visibility of records,
but some others DBMS used indexes without problems(for example
FireBird)? Or maybe some another information be helpful for me and
community.
--
С наилучшими пожеланиями,
Репко Андрей Владимирович mailto:repko@sart.must-ipra.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used on group by
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
@ 2005-09-27 10:48 ` Richard Huxton <dev@archonet.com>
2005-09-27 10:57 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-09-27 10:48 UTC (permalink / raw)
To: Андрей Репко <repko@sart.must-ipra.com>; +Cc: pgsql-performance
Andrey Repko wrote:
>
> I have table ma_data, that contain above 300000 rows.
> This table has primary key id, and field alias_id.
> I create index (btree)on this field.
> Set statistic:
>
> ALTER TABLE "public"."ma_data"
> ALTER COLUMN "alias_id" SET STATISTICS 998;
>
> So, when I do something like
> SELECT alias_id FROM ma_data GROUP BY alias_id
Why are you using GROUP BY without any aggregate functions?
What happens if you use something like
SELECT DISTINCT alias_id FROM ma_data;
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used on group by
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
@ 2005-09-27 10:57 ` Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 11:08 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Андрей Репко @ 2005-09-27 10:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Здравствуйте Richard,
Tuesday, September 27, 2005, 1:48:15 PM, Вы писали:
RH> Andrey Repko wrote:
>>
>> I have table ma_data, that contain above 300000 rows.
>> This table has primary key id, and field alias_id.
>> I create index (btree)on this field.
>> Set statistic:
>>
>> ALTER TABLE "public"."ma_data"
>> ALTER COLUMN "alias_id" SET STATISTICS 998;
>>
>> So, when I do something like
>> SELECT alias_id FROM ma_data GROUP BY alias_id
RH> Why are you using GROUP BY without any aggregate functions?
RH> What happens if you use something like
RH> SELECT DISTINCT alias_id FROM ma_data;
sart_ma=# EXPLAIN ANALYZE SELECT DISTINCT alias_id FROM ma_data;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Unique (cost=65262.63..66770.75 rows=32 width=4) (actual time=16780.214..18250.761 rows=32 loops=1)
-> Sort (cost=65262.63..66016.69 rows=301624 width=4) (actual time=16780.204..17255.129 rows=301624 loops=1)
Sort Key: alias_id
-> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624 width=4) (actual time=6.896..15321.023 rows=301624 loops=1)
Total runtime: 18292.542 ms
(5 rows)
sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=38565.30..38565.62 rows=32 width=4) (actual time=15990.863..15990.933 rows=32 loops=1)
-> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624 width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
Total runtime: 15991.244 ms
(3 rows)
--
С наилучшими пожеланиями,
Репко Андрей Владимирович mailto:repko@sart.must-ipra.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used on group by
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
2005-09-27 10:57 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
@ 2005-09-27 11:08 ` Richard Huxton <dev@archonet.com>
2005-09-27 11:37 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-09-27 11:08 UTC (permalink / raw)
To: Андрей Репко <repko@sart.must-ipra.com>; +Cc: pgsql-performance
Андрей Репко wrote:
> RH> What happens if you use something like
> RH> SELECT DISTINCT alias_id FROM ma_data;
> sart_ma=# EXPLAIN ANALYZE SELECT DISTINCT alias_id FROM ma_data;
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=65262.63..66770.75 rows=32 width=4) (actual time=16780.214..18250.761 rows=32 loops=1)
> -> Sort (cost=65262.63..66016.69 rows=301624 width=4) (actual time=16780.204..17255.129 rows=301624 loops=1)
> Sort Key: alias_id
> -> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624 width=4) (actual time=6.896..15321.023 rows=301624 loops=1)
> Total runtime: 18292.542 ms
> sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=38565.30..38565.62 rows=32 width=4) (actual time=15990.863..15990.933 rows=32 loops=1)
> -> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624 width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
> Total runtime: 15991.244 ms
OK - the planner thinks it's doing the right thing, your cost estimates
are way off. If you look back at where you got an index-scan, it's cost
was 1.1 million.
Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57
That's way above the numbers for seq-scan+hash/sort, so if the cost
estimate was right PG would be making the right choice. Looks like you
need to check your configuration settings. Have you read:
http://www.powerpostgresql.com/PerfList
or
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used on group by
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
2005-09-27 10:57 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 11:08 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
@ 2005-09-27 11:37 ` Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 14:34 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Андрей Репко @ 2005-09-27 11:37 UTC (permalink / raw)
To: pgsql-performance
Здравствуйте Richard,
Tuesday, September 27, 2005, 2:08:31 PM, Вы писали:
>> sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
>> QUERY PLAN
>> -------------------------------------------------------------------------------------------------------------------------
>> HashAggregate (cost=38565.30..38565.62 rows=32 width=4)
>> (actual time=15990.863..15990.933 rows=32 loops=1)
>> -> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624
>> width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
>> Total runtime: 15991.244 ms
RH> OK - the planner thinks it's doing the right thing, your cost estimates
RH> are way off. If you look back at where you got an index-scan, it's cost
RH> was 1.1 million.
RH> Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57
But why PG scan _all_ the records in the table? As I understand we can
"just" select information from index, not scaning all the table? Of
course if we select ALL records from table index can't help us.
If I write something like:
SELECT (SELECT alias_id FROM ma_data WHERE alias_id =1 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =2 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =3 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =4 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =5 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =6 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =7 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =8 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =9 LIMIT 1)
...
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id=max_alias_id LIMIT 1)
It works better, much better.
RH> That's way above the numbers for seq-scan+hash/sort, so if the cost
RH> estimate was right PG would be making the right choice. Looks like you
RH> need to check your configuration settings. Have you read:
RH> http://www.powerpostgresql.com/PerfList
RH> or
RH> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
Thanks.
--
С наилучшими пожеланиями,
Репко Андрей Владимирович mailto:repko@sart.must-ipra.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used on group by
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
2005-09-27 10:57 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 11:08 ` Re: Index not used on group by Richard Huxton <dev@archonet.com>
2005-09-27 11:37 ` Re: Index not used on group by Андрей Репко <repko@sart.must-ipra.com>
@ 2005-09-27 14:34 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2005-09-27 14:34 UTC (permalink / raw)
To: Андрей Репко <repko@sart.must-ipra.com>; +Cc: pgsql-performance
Андрей Репко wrote:
> Здравствуйте Richard,
>
> Tuesday, September 27, 2005, 2:08:31 PM, Вы писали:
>
>
>
>>>sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
>>> QUERY PLAN
>>>-------------------------------------------------------------------------------------------------------------------------
>>> HashAggregate (cost=38565.30..38565.62 rows=32 width=4)
>>>(actual time=15990.863..15990.933 rows=32 loops=1)
>>> -> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624
>>>width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
>>> Total runtime: 15991.244 ms
>
>
> RH> OK - the planner thinks it's doing the right thing, your cost estimates
> RH> are way off. If you look back at where you got an index-scan, it's cost
> RH> was 1.1 million.
> RH> Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57
> But why PG scan _all_ the records in the table? As I understand we can
> "just" select information from index, not scaning all the table? Of
> course if we select ALL records from table index can't help us.
Actually, if you select more than 5-10% of the rows (in general) you are
better off using a seq-scan.
PostgreSQL estimates the total cost of possible query plans and picks
the cheapest. In your case your configuration settings seem to be
pushing the cost of an index scan much higher than it is. So, it picks
the sequential-scan.
> If I write something like:
> SELECT (SELECT alias_id FROM ma_data WHERE alias_id =1 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =2 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =3 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =4 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =5 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =6 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =7 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =8 LIMIT 1)
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =9 LIMIT 1)
> ...
> UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id=max_alias_id LIMIT 1)
> It works better, much better.
Of course - it will always choose index queries here - it can see you
are only fetching one row in each subquery.
Correct your configuration settings so PG estimates the cost of an index
query correctly and all should be well.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-09-27 14:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-27 09:14 Index not used on group by Andrey Repko <repko@sart.must-ipra.com>
2005-09-27 10:48 ` Richard Huxton <dev@archonet.com>
2005-09-27 10:57 ` Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 11:08 ` Richard Huxton <dev@archonet.com>
2005-09-27 11:37 ` Андрей Репко <repko@sart.must-ipra.com>
2005-09-27 14:34 ` 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