[HACKERS] Performance Issues
I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Thanks Dhanaraj ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Performance Issues
Dhanaraj M [EMAIL PROTECTED] writes: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. To enforce the primary key constraint, PG creates a unique index when the table is created (I think it even tells you this after CREATE TABLE). 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. All kinds of data is cached in shared memory. Did you tune the shared_buffers setting in postgresql.conf? It's set quite low by default to make sure the server can start on systems with low shared memory limits. The online documentation has this info and lots more--I suggest you read it. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance Issues
Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance Issues
On 23-May-06, at 10:24 AM, Richard Huxton wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. Postgresql relies on the kernel buffers, and shared buffers for caching. As someone else said postgresql is quite conservative when shipped. Tuning helps considerably Dave -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Performance Issues
Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. Well, here is an interesting question that I have suddenly become very curious of, if you have a primary key, obviously a unique index, is it, in fact, use this index regardless of analyzing the table? 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. I am looking at this string of posts and it occurs to me that he should run analyze. Maybe I'm jumping at the wrong point. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance Issues
Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. Mark Woodward wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. Well, here is an interesting question that I have suddenly become very curious of, if you have a primary key, obviously a unique index, is it, in fact, use this index regardless of analyzing the table? 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. I am looking at this string of posts and it occurs to me that he should run analyze. Maybe I'm jumping at the wrong point. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Performance Issues
Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Performance Issues
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. And every other version too. PRIMARY KEY/UNIQUE syntax was not supported before this patch: 1997-12-04 18:07 thomas * src/backend/parser/: analyze.c, gram.y: Add SQL92-compliant syntax for constraints. Implement PRIMARY KEY and UNIQUE clauses using indices. and in that patch and every subsequent version, unique constraints are associated with indexes. In fact, we do not even *have* any implementation method for unique constraints other than the duplicate- entry-detection code in the btree index AM. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] []performance issues
So I am still interested in PostgreSQL's ability to deal with multimillon records tables. Postgres has no problem with multimillion row tables - many people on this list run them - just don't do sequential scans on them if you can't afford the time it takes. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] []performance issues
Hello, Sorry if it's wrong list for the question. Could you suggest some tweaks to the PostgreSQL 7.2.1 to handle the following types of tables faster? Here we have table stats with something over one millon records. Obvious SELECT COUNT(*) FROM stats takes over 40 seconds to execute, and this amount of time does not shorten considerably in subsequent similar requests. All the databases are vacuumed nightly. CREATE TABLE stats ( url varchar(50), src_port varchar(10), ip varchar(16), dst_port varchar(10), proto varchar(10), size int8, login varchar(20), start_date timestamptz, end_date timestamptz, aggregated int4 ); CREATE INDEX aggregated_stats_key ON stats (aggregated); CREATE INDEX ip_stats_key ON stats (ip); stats= explain select count(*) from stats; NOTICE: QUERY PLAN: Aggregate (cost=113331.10..113331.10 rows=1 width=0) - Seq Scan on stats (cost=0.00..110085.28 rows=1298328 width=0) EXPLAIN stats= select count(*) from stats; count - 1298328 (1 row) The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb DDR, ATA 100 HDD. Thanks in advance, Yar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] []performance issues
Here we have table stats with something over one millon records. Obvious SELECT COUNT(*) FROM stats takes over 40 seconds to execute, and this amount of time does not shorten considerably in subsequent similar requests. All the databases are vacuumed nightly. Doing a row count requires a sequential scan in Postgres. Try creating another summary table that just has one row and one column and is an integer. Then, create a trigger on your stats table that fires whenever a new row is added or deleted and updates the tally of rows in the summary table. Then, just select from the summary table to get an instantaneous count. Of course, insert and deletes will be marginally slowed down. Refer to the docs for CREATE TRIGGER, CREATE FUNCTION and PL/PGSQL for more info on how to do this. Regards, Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] []performance issues
Christopher Kings-Lynne wrote: Doing a row count requires a sequential scan in Postgres. Try creating another summary table that just has one row and one column and is an integer. I have THREE summary tables derived from stats with different levels of aggregation. They work quite fast, But: 1) Summary tables grow too 2) There are requests which cannot be predicted, so they involve the stats table itself. So I am still interested in PostgreSQL's ability to deal with multimillon records tables. Best regards, Yar. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] []performance issues
On Fri, Aug 02, 2002 at 03:48:39PM +0400, Yaroslav Dmitriev wrote: So I am still interested in PostgreSQL's ability to deal with multimillon records tables. [x-posted and Reply-To: to -general; this isn't a development problem.] We have tables with multimillion records, and they are fast. But not fast to count(). The MVCC design of PostgreSQL will give you very few concurerncy problems, but you pay for that in the response time of certain kinds of aggregates, which cannot use an index. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
