pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance vs Schemas
5+ messages / 5 participants
[nested] [flat]
* Performance vs Schemas
@ 2004-10-14 18:38 Igor Maciel Macaubas <igor@providerst.com.br>
0 siblings, 2 replies; 5+ messages in thread
From: Igor Maciel Macaubas @ 2004-10-14 18:38 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I recently migrated my database from schema 'public' to multiple schema.
I have around 100 tables, and divided them in 14 different schemas, and then adapted my application to use schemas as well.
I could percept that the query / insert / update times get pretty much faster then when I was using the old unique schema, and I'd just like to confirm with you if using schemas speed up the things. Is that true ?
What else I can do to speed up the query processing, best pratices, recommendations ... ? What about indexed views, does postgresql supports it?
Regards,
Igor
--
igor@providerst.com.br
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance vs Schemas
@ 2004-10-14 18:45 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gregory S. Williamson @ 2004-10-14 18:45 UTC (permalink / raw)
To: Igor Maciel Macaubas <igor@providerst.com.br>; pgsql-performance
Igor,
I'm not sure if it is proper to state that schemas are themselves speeding things up.
As an example, we have data that is usually accessed by county; when we put all of the data into one big table and select from it using a code for a county of interest, the process is fairly slow as there are several hundred thousand candidate rows from that county in a table with many millions of rows. When we broke out certain aspects of the data into schemas (one per county) the searches become very fast indeed because we can skip the searching for a specific county code with the relevant tables and there is less (unneeded) data in the table being searched.
As always, "EXPLAIN ANALYZE ..." is your friend in understanding what the planner is doing with a given query.
See <http://www.varlena.com/varlena/GeneralBits/Tidbits/; for some useful information, especially under the performance tips section.
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: Igor Maciel Macaubas [mailto:igor@providerst.com.br]
Sent: Thu 10/14/2004 11:38 AM
To: pgsql-performance@postgresql.org
Cc:
Subject: [PERFORM] Performance vs Schemas
Hi all,
I recently migrated my database from schema 'public' to multiple schema.
I have around 100 tables, and divided them in 14 different schemas, and then adapted my application to use schemas as well.
I could percept that the query / insert / update times get pretty much faster then when I was using the old unique schema, and I'd just like to confirm with you if using schemas speed up the things. Is that true ?
What else I can do to speed up the query processing, best pratices, recommendations ... ? What about indexed views, does postgresql supports it?
Regards,
Igor
--
igor@providerst.com.br
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance vs Schemas
@ 2004-10-14 19:27 Aaron Werman <awerman@hotmail.com>
parent: Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 5+ messages in thread
From: Aaron Werman @ 2004-10-14 19:27 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; Igor Maciel Macaubas <igor@providerst.com.br>; pgsql-performance
Right - if you split a table to a lot of more selective tables, it can often
dramatically change the plan options (e.g. - in a single table, selectivity
for a query may be 1% and require an expensive nested loop while in the more
restrictive table it may match 14% of the data and do a cheaper scan).
Also - don't forget that just rebuilding a database cleanly can dramatically
improve performance.
The only dbms I know that indexes views is MS SQL Server 2000, where it is a
limited form of materialized queries. pg doesn't do MQs, but check out
functional indices.
/Aaron
----- Original Message -----
From: "Gregory S. Williamson" <gsw@globexplorer.com>
To: "Igor Maciel Macaubas" <igor@providerst.com.br>;
<pgsql-performance@postgresql.org>
Sent: Thursday, October 14, 2004 2:45 PM
Subject: Re: [PERFORM] Performance vs Schemas
Igor,
I'm not sure if it is proper to state that schemas are themselves speeding
things up.
As an example, we have data that is usually accessed by county; when we put
all of the data into one big table and select from it using a code for a
county of interest, the process is fairly slow as there are several hundred
thousand candidate rows from that county in a table with many millions of
rows. When we broke out certain aspects of the data into schemas (one per
county) the searches become very fast indeed because we can skip the
searching for a specific county code with the relevant tables and there is
less (unneeded) data in the table being searched.
As always, "EXPLAIN ANALYZE ..." is your friend in understanding what the
planner is doing with a given query.
See <http://www.varlena.com/varlena/GeneralBits/Tidbits/; for some useful
information, especially under the performance tips section.
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: Igor Maciel Macaubas [mailto:igor@providerst.com.br]
Sent: Thu 10/14/2004 11:38 AM
To: pgsql-performance@postgresql.org
Cc:
Subject: [PERFORM] Performance vs Schemas
Hi all,
I recently migrated my database from schema 'public' to multiple schema.
I have around 100 tables, and divided them in 14 different schemas, and then
adapted my application to use schemas as well.
I could percept that the query / insert / update times get pretty much
faster then when I was using the old unique schema, and I'd just like to
confirm with you if using schemas speed up the things. Is that true ?
What else I can do to speed up the query processing, best pratices,
recommendations ... ? What about indexed views, does postgresql supports it?
Regards,
Igor
--
igor@providerst.com.br
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance vs Schemas
@ 2004-10-15 00:52 Neil Conway <neilc@samurai.com>
parent: Igor Maciel Macaubas <igor@providerst.com.br>
1 sibling, 0 replies; 5+ messages in thread
From: Neil Conway @ 2004-10-15 00:52 UTC (permalink / raw)
To: Igor Maciel Macaubas <igor@providerst.com.br>; +Cc: pgsql-performance
On Fri, 2004-10-15 at 04:38, Igor Maciel Macaubas wrote:
> I have around 100 tables, and divided them in 14 different schemas,
> and then adapted my application to use schemas as well.
> I could percept that the query / insert / update times get pretty much
> faster then when I was using the old unique schema, and I'd just like
> to confirm with you if using schemas speed up the things. Is that true
> ?
Schemas are a namespacing technique; AFAIK they shouldn't significantly
affect performance (either positively or negatively).
> What about indexed views, does postgresql supports it?
No, you'll need to create indexes on the view's base tables.
-Neil
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance vs Schemas
@ 2004-10-15 01:41 Iain <iain@mst.co.jp>
parent: Igor Maciel Macaubas <igor@providerst.com.br>
1 sibling, 0 replies; 5+ messages in thread
From: Iain @ 2004-10-15 01:41 UTC (permalink / raw)
To: Igor Maciel Macaubas <igor@providerst.com.br>; pgsql-performance
Hi Igor,
I expect that when you moved your tables to different schemas that you
effectively did a physical re-organization (ie unload/reload of the tables).
It's nothing to do with the use of schemas as such. If you had reloaded your
tables into the same system schema you would have experienced the same
speedup as the data tables would be more compact.
regards
Iain
----- Original Message -----
From: Igor Maciel Macaubas
To: pgsql-performance@postgresql.org
Sent: Friday, October 15, 2004 3:38 AM
Subject: [PERFORM] Performance vs Schemas
Hi all,
I recently migrated my database from schema 'public' to multiple schema.
I have around 100 tables, and divided them in 14 different schemas, and
then adapted my application to use schemas as well.
I could percept that the query / insert / update times get pretty much
faster then when I was using the old unique schema, and I'd just like to
confirm with you if using schemas speed up the things. Is that true ?
What else I can do to speed up the query processing, best pratices,
recommendations ... ? What about indexed views, does postgresql supports it?
Regards,
Igor
--
igor@providerst.com.br
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-10-15 01:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-14 18:38 Performance vs Schemas Igor Maciel Macaubas <igor@providerst.com.br>
2004-10-15 00:52 ` Neil Conway <neilc@samurai.com>
2004-10-15 01:41 ` Iain <iain@mst.co.jp>
2004-10-14 18:45 Re: Performance vs Schemas Gregory S. Williamson <gsw@globexplorer.com>
2004-10-14 19:27 ` Aaron Werman <awerman@hotmail.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