Re: [PERFORM] Index scan / Index cond limitation or ?
15.10.2010 22:29, Tom Lane: Nikolai Zhubrn-a-zh...@yandex.ru writes: So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? It thinks the indexscan condition is sufficiently selective already. An = ANY condition like that will force multiple index searches, one for each of the OR'd possibilities, so it's far from free to add it to the index condition. The planner doesn't think it's worth it. Aha, ok. It makes sense then. Is this specific case (=ANY in index cond) described somewhere with reasonable detail? I always try to RTFM first and most of the time I can find pretty good hints in the regular manual already (sufficient as a starting point at least) but this specific topic seems to be somewhat mysterious. Perhaps on your real query it is, but there's not much point in debating about the behavior on this toy table; without realistic table sizes and up-to-date stats it's impossible to say whether that choice is correct or not. SELECT version(); PostgreSQL 8.3.1, compiled by Visual C++ build 1400 You really, really, really ought to be running 8.3.something-newer. We didn't put out the last 11 8.3.x bugfix updates just because we didn't have anything better to do. Yes, I know, and I do appreciate the efforts of postgresql devels to create updates for older versions too. This server is internal-only (so it does not see any real world yet). Anyway, I hope to update everything to 9.0.1 soon. Thank you! Nikolai regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Help with duration of statement: EXECUTE unnamed [PREPARE: COMMIT]
We currently have log_min_duration_statement = 5000 and are seeing statements like the following logged 2010-10-16 05:55:52 EDT [6334]: [1-1] LOG: duration: 5572.517 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 06:06:24 EDT [26856]: [1-1] LOG: duration: 5617.866 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 06:06:24 EDT [20740]: [13-1] LOG: duration: 5210.190 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 08:24:06 EDT [8743]: [1-1] LOG: duration: 6487.346 ms statement: EXECUTE unnamed [PREPARE: COMMIT] Questions; 1) What do these statements mean? 2) How do I dig deeper to determine why they are taking longer than 5 secs. Version Info -- select version(); version PostgreSQL 8.1.17 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Select count(*), the sequel
There was some doubt as for the speed of doing the select count(*) in
PostgreSQL and Oracle.
To that end, I copied the most part of the Oracle table I used before to
Postgres. Although the copy
wasn't complete, the resulting table is already significantly larger
than the table it was copied from. The result still shows that Oracle is
significantly faster:
Oracle result:
SQL alter system flush buffer_cache;
System altered.
SQL select /*+ full(NO) noparallel */ count(*) from ni_occurrence no;
COUNT(*)
--
402062638
Elapsed: 00:03:16.45
Hints are necessary because Oracle table is declared as parallel and I
didn't want the PK index to be used for counting. Oracle has a good
habit of using PK's for counting, if available.
SQL select bytes/1048576 as MB
2 from user_segments
3 where segment_name='NI_OCCURRENCE';
MB
--
35329
Elapsed: 00:00:00.85
SQL
So, oracle stores 402 million records in 35GB and counts them in 3
minutes 16.45 seconds The very same table was partially copied to
Postgres, copying died with ORA-01555 snapshot too old sometimes this
morning. I ran vacuumdb -f -z on the database after the copy completed
and the results are below.
mgogala=# select count(*) from ni_occurrence;
count
---
382400476
(1 row)
Time: 221716.466 ms
mgogala=#
mgogala=# select 221/60::real;
?column?
--
3.68
(1 row)
Time: 0.357 ms
mgogala=#
mgogala=# select pg_size_pretty(pg_table_size('ni_occurrence'));
pg_size_pretty
46 GB
(1 row)
Time: 0.420 ms
mgogala=#
The database wasn't restarted, no caches were flushed, the comparison
was done with a serious advantage for PostgreSQL. Postgres needed 3.68
minutes to complete the count which is about the same Oracle but still
somewhat slower. Also, I am worried about the sizes. Postgres table is
11GB larger than the original, despite having less data. That was an
unfair and unbalanced comparison because Oracle's cache was flushed and
Oracle was artificially restrained to use the full table scan without
the aid of parallelism. Here is the same result, with no hints and the
autotrace on, which shows what happens if I turn the hints off:
SQL select count(*) from ni_occurrence no;
COUNT(*)
--
402062638
Elapsed: 00:00:52.61
Execution Plan
--
Plan hash value: 53476935
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | |1 | 54001 (19)|
00:01:08 | | | |
| 1 | SORT AGGREGATE | |1 | |
| | | |
| 2 | PX COORDINATOR | | | |
| | | |
| 3 |PX SEND QC (RANDOM) | :TQ1 |1 | |
| Q1,00 | P-S | QC (RAND) |
| 4 | SORT AGGREGATE | |1 | |
| Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 402M| 54001 (19)|
00:01:08 | Q1,00 | PCWC | |
| 6 | INDEX FAST FULL SCAN| IDX_NI_OCCURRENCE_PID | 402M|
54001 (19)|
00:01:08 | Q1,00 | PCWP | |
It took just 52 seconds to count everything, but Oracle didn't even scan
the table, it scanned a unique index, in parallel. That is the
algorithmic advantage that forced me to restrict the execution plan with
hints. My conclusion is that the speed of the full scan is OK, about the
same as Oracle speed. There are, however, three significant algorithm
advantages on the Oracle's side:
1) Oracle can use indexes to calculate select count
2) Oracle can use parallelism.
3) Oracle can use indexes in combination with the parallel processing.
Here are the descriptions:
SQL desc ni_occurrence
Name Null?Type
-
ID NOT NULL NUMBER(22)
PERMANENT_ID NOT NULL VARCHAR2(12)
CALL_LETTERS NOT NULL VARCHAR2(5)
AIRDATE NOT NULL DATE
DURATION NOT NULL NUMBER(4)
PROGRAM_TITLEVARCHAR2(360)
COSTNUMBER(15)
ASSETIDNUMBER(12)
MARKET_IDNUMBER
GMT_TIMEDATE
ORIG_ST_OCC_ID NUMBER
EPISODEVARCHAR2(450)
IMPRESSIONSNUMBER
SQL
Re: [PERFORM] No hash join across partitioned tables?
On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera alvhe...@commandprompt.comwrote: Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010: An issue with automatically analyzing the entire hierarchy is 'abstract' table definitions. I've got a set of tables for storing the same data at different granularities of aggregation. Within each granularity, I've got partitions, but because the set of columns is identical for each granularity, I've got an abstract table definition that is inherited by everything. I don't need or want statistics kept on that table because I never query across the abstract table, only the parent table of each aggregation granularity Hmm, I think you'd be better served by using LIKE instead of regular inheritance. Yep. I inherited the architecture, though, and changing it hasn't been a high priority. --sam
Re: [PERFORM] Select count(*), the sequel
16.10.10 19:51, Mladen Gogala написав(ла): There was some doubt as for the speed of doing the select count(*) in PostgreSQL and Oracle. To that end, I copied the most part of the Oracle table I used before to Postgres. Although the copy wasn't complete, the resulting table is already significantly larger than the table it was copied from. The result still shows that Oracle is significantly faster: Hello. Did you vacuum postgresql DB before the count(*). I ask this because (unless table was created loaded in same transaction) on the first scan, postgresql has to write hint bits to the whole table. Second scan may be way faster. Best regards, Vitalii Tymchyshyn -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Select count(*), the sequel
On 10/18/2010 3:58 AM, Vitalii Tymchyshyn wrote: Hello. Did you vacuum postgresql DB before the count(*). I ask this because (unless table was created loaded in same transaction) on the first scan, postgresql has to write hint bits to the whole table. Second scan may be way faster. Best regards, Vitalii Tymchyshyn Vitalli, yes I did vacuum before the count. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Help with duration of statement: EXECUTE unnamed [PREPARE: COMMIT]
Eric Comeau ecom...@signiant.com writes: 2010-10-16 05:55:52 EDT [6334]: [1-1] LOG: duration: 5572.517 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 06:06:24 EDT [26856]: [1-1] LOG: duration: 5617.866 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 06:06:24 EDT [20740]: [13-1] LOG: duration: 5210.190 ms statement: EXECUTE unnamed [PREPARE: COMMIT] 2010-10-16 08:24:06 EDT [8743]: [1-1] LOG: duration: 6487.346 ms statement: EXECUTE unnamed [PREPARE: COMMIT] Questions; 1) What do these statements mean? They appear to be COMMIT commands. (It's pretty stupid to be using the PREPARE/EXECUTE machinery to execute a COMMIT, but that's evidently what your client-side code is doing.) 2) How do I dig deeper to determine why they are taking longer than 5 secs. My guess would be overstressed disk subsystem. A COMMIT doesn't require much except fsync'ing the commit WAL record down to disk ... but if the disk is too busy to process that request quickly, you might have to wait. It also seems possible that the filesystem is interlocking the fsync on the WAL file with previous writes to other files. Anyway, watching things with vmstat or iostat to see if there's an activity spike when this is happening would confirm or refute that idea. [ thinks for a bit ... ] Actually, it's possible that the COMMIT command is doing nontrivial work before it can really commit. Perhaps you have deferred foreign keys to check? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Help with duration of statement: EXECUTE unnamed [PREPARE: COMMIT]
Tom Lane wrote: My guess would be overstressed disk subsystem. A COMMIT doesn't require much except fsync'ing the commit WAL record down to disk ... Doesn't the commit statement also release all the locks held by the transaction? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] No hash join across partitioned tables?
Excerpts from Samuel Gendler's message of lun oct 18 03:13:01 -0300 2010: On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera alvhe...@commandprompt.comwrote: Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010: An issue with automatically analyzing the entire hierarchy is 'abstract' table definitions. I've got a set of tables for storing the same data at different granularities of aggregation. Within each granularity, I've got partitions, but because the set of columns is identical for each granularity, I've got an abstract table definition that is inherited by everything. I don't need or want statistics kept on that table because I never query across the abstract table, only the parent table of each aggregation granularity Hmm, I think you'd be better served by using LIKE instead of regular inheritance. Yep. I inherited the architecture, though, and changing it hasn't been a high priority. I understand that; my point is merely that maybe we shouldn't work through many hoops to solve this particular facet of the problem, because it seems to be pilot error. (If you really needed to avoid the extra I/O that would be caused by unnecessary analyzes, you could turn autovac off for the abstract tables). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Help with duration of statement: EXECUTE unnamed [PREPARE: COMMIT]
Mladen Gogala mladen.gog...@vmsinfo.com writes: Tom Lane wrote: My guess would be overstressed disk subsystem. A COMMIT doesn't require much except fsync'ing the commit WAL record down to disk ... Doesn't the commit statement also release all the locks held by the transaction? Yeah, and there's a nontrivial amount of other cleanup too, but it all amounts to just changes in in-memory data structures. I don't see that taking five seconds, especially not if commits of similar transactions usually take much less than that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] how to get the total number of records in report
On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman rumman...@gmail.com wrote: At present for reporting I use following types of query: select crm.*, crm_cnt.cnt from crm, (select count(*) as cnt from crm) crm_cnt; Here count query is used to find the total number of records. Same FROM clause is copied in both the part of the query. Is there any other good alternative way to get this similar value? Well, it looks like you're currently executing two sequential scans over the crm table. And you're including the total row-count as a separate column in every row you get back, although you really only need this piece of information once. Since you're fetching all of the crm table anyway, why not get rid of the COUNT(*) entirely and just keep a count on the client-side of the total number of rows you've fetched? Josh -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] unexpected query failure: ERROR: GIN indexes do not support whole-index scans
I have a table with an array column. I added a GIN index to the array: CREATE INDEX foo_idx ON t USING GIN (alternatecodes) WHERE alternatecodes IS NOT NULL; That's all well and good. However, some queries started failing and I was able to reproduce the behavior in psql! SELECT * FROM t WHERE alternatecodes IS NOT NULL; returns: ERROR: GIN indexes do not support whole-index scans Whaaa? Adding an *index* makes my /queries/ stop working? How can this be? This really violated my principle of least surprise. If GIN indexes don't support whole-index scans, fine, don't use them, but don't make a perfectly valid query fail because of it. This seems like a bug. Is it? PostgreSQL version: PostgreSQL 8.4.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] unexpected query failure: ERROR: GIN indexes do not support whole-index scans
Jon Nelson jnelson+pg...@jamponi.net writes: CREATE INDEX foo_idx ON t USING GIN (alternatecodes) WHERE alternatecodes IS NOT NULL; SELECT * FROM t WHERE alternatecodes IS NOT NULL; ERROR: GIN indexes do not support whole-index scans Yep, this is a known issue. It's going to take major surgery on GIN to fix it, so don't hold your breath. In the particular case, what good do you think the WHERE clause is doing anyway? GIN won't index nulls at all ... which indeed is an aspect of the underlying issue --- see recent discussions, eg here: http://archives.postgresql.org/pgsql-hackers/2010-10/msg00521.php regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
8.4.5 I consistently see HashJoin plans that hash the large table, and scan the small table. This is especially puzzling in some cases where I have 30M rows in the big table and ~ 100 in the small... shouldn't it hash the small table and scan the big one? Here is one case I saw just recently Hash Cond: ((a.e_id)::text = (ta.name)::text) - Index Scan using c_a_s_e_id on a (cost=0.00..8.21 rows=14 width=27) Index Cond: (id = 12) - Hash (cost=89126.79..89126.79 rows=4825695 width=74) - Seq Scan on p_a_1287446030 tmp (cost=0.00..89126.79 rows=4825695 width=74) Filter: (id = 12) Does this ever make sense? Isn't it always better to hash the smaller side of the join, or at least predominantly so? Maybe if you want the order of elements returning from the join to coincide with the order of the outer part of the join for a join higher up the plan tree. in this specific case, I want the order to be based on the larger table for the join higher up (not shown) in the plan so that its index scan is in the order that tmp already is. Certainly, for very small hash tables ( 1000 entries) the cache effects strongly favor small tables -- the lookup should be very cheap. Building a very large hash is not cheap, and wastes lots of memory. I suppose at very large sizes something else might come into play that favors hashing the bigger table, but I can't think of what that would be for the general case. Any ideas? I've seen this with dozens of queries, some simple, some with 5 or 6 tables and joins. I even tried making work_mem very small in a 30M row to 500 row join, and it STILL hashed the big table. At first I thought that I was reading the plan wrong, but google suggests its doing what it looks like its doing. Perhaps this is a bug? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] unexpected query failure: ERROR: GIN indexes do not support whole-index scans
On Mon, Oct 18, 2010 at 6:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jon Nelson jnelson+pg...@jamponi.net writes: CREATE INDEX foo_idx ON t USING GIN (alternatecodes) WHERE alternatecodes IS NOT NULL; SELECT * FROM t WHERE alternatecodes IS NOT NULL; ERROR: GIN indexes do not support whole-index scans Yep, this is a known issue. It's going to take major surgery on GIN to fix it, so don't hold your breath. In the particular case, what good do you think the WHERE clause is doing anyway? GIN won't index nulls at all ... which indeed is an aspect of the underlying issue --- see recent discussions, eg here: http://archives.postgresql.org/pgsql-hackers/2010-10/msg00521.php OK, so GIN doesn't index NULLs. I guess the IS NOT NULL part comes about as a habit - that particular column is fairly sparse. However, I'm honestly quite surprised at two things: 1. if GIN indexes ignore NULLs, then either it should grump when one specifics WHERE ... IS NOT NULL or it should be treated as a no-op 2. (and this is by far the more surprising) that the /presence/ of an INDEX can *break* a SELECT. It's not that the engine ignores the index - that would be reasonable - but that I can't issue a SELECT with a WHERE statement that matches the same as the index. However, I see that this also surprised Josh Berkus, and not that long ago (11 days!), so I'll just shush. Thanks! -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
Scott Carey sc...@richrelevance.com writes: I consistently see HashJoin plans that hash the large table, and scan the small table. Could we see a self-contained test case? And what cost parameters are you using, especially work_mem? This is especially puzzling in some cases where I have 30M rows in the big table and ~ 100 in the small... shouldn't it hash the small table and scan the big one? Well, size of the table isn't the only factor; in particular, a highly nonuniform distribution of the key value will inflate the cost estimate for using a table on the inner size of the hash. But the example you show here seems a bit extreme. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
