pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance tradeoff
2+ messages / 2 participants
[nested] [flat]
* Performance tradeoff
@ 2005-03-02 17:52 Shawn Chisholm <schisholm@sandvine.com>
2005-03-03 05:31 ` Re: Performance tradeoff Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Shawn Chisholm @ 2005-03-02 17:52 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I am wondering about the relative performance of "insert into table1 select distinct a,b from ..." and "insert into table1 select a,b from ... group by a,b" when querying tables of different sizes (10K, 100K, 1s, 10s, 100s of millions of rows).
The distinct way tends to sort/unique and the group by tends to hash aggregate... any opinions on which is better?
I can also change the schema to a certain extent, so would it be worthwhile to put indices on the queried tables (or refactor them) hoping the distinct does an index scan instead of sort... would the query planner take advantage of that?
Thanks,
Shawn
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance tradeoff
2005-03-02 17:52 Performance tradeoff Shawn Chisholm <schisholm@sandvine.com>
@ 2005-03-03 05:31 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2005-03-03 05:31 UTC (permalink / raw)
To: Shawn Chisholm <schisholm@sandvine.com>; +Cc: pgsql-performance
Shawn,
> I can also change the schema to a certain extent, so would it be worthwhile
> to put indices on the queried tables (or refactor them) hoping the distinct
> does an index scan instead of sort... would the query planner take
> advantage of that?
Use the GROUP BY, with an index on the grouped columns and lots of work_mem
(sort_mem in 7.4). This will give the planner the option of a hashaggregate
which could be significantly faster than the other methods.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-03 05:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-02 17:52 Performance tradeoff Shawn Chisholm <schisholm@sandvine.com>
2005-03-03 05:31 ` Josh Berkus <josh@agliodbs.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