Re: [PERFORM] work_mem and shared_buffers
Bill Moran a écrit : On Fri, 9 Nov 2007 12:08:57 -0600 Campbell, Lance [EMAIL PROTECTED] wrote: How do you know when you should up the value of work_mem? Just play with the number. Is there a query I could do that would tell me if PostgreSql is performing SQL that could use more memory for sorting? 8.2 and older, it can be difficult to know, and I don't have a specific recommendation. I haven't use it in that context before, but perhaps inotify can be used to catch postgresql usage of temp files. ( http://inotify.aiken.cz/ , http://inotify.aiken.cz/?section=incronpage=aboutlang=en ) 8.3 includes a parameter to log the usage of temporary files by Postgres. When a sort can't fit in the available memory, it uses a temp file, thus you could use this new feature to track when sorts don't fit in work_mem. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
Steinar H. Gunderson wrote: On Sun, Nov 11, 2007 at 08:27:02PM +0100, Dimitri wrote: As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I guess it's because their Niagara support is still very raw, and besides, it's not a very common platform. /* Steinar */ Not sure how much coding would need to be done for Niagra chips but I would think that it is more likely a problem of getting the funds so they can have one to work on. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] difference between a unique constraint and a unique index ???
In order to get like queries to use an index with database initialized with a UTF-8 character set I added a unique index to a table with a varchar_pattern_ops This table already had a unique constraint on the column so I dropped the unique constraint. I can't give exact measurements however this caused my application to slow down considerably. The only thing I can figure is that the varchar_pattern_ops operator is significantly slower ??? Is there some other piece of the puzzle to fill in ? Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] difference between a unique constraint and a unique index ???
Dave Cramer wrote: In order to get like queries to use an index with database initialized with a UTF-8 character set I added a unique index to a table with a varchar_pattern_ops This table already had a unique constraint on the column so I dropped the unique constraint. I can't give exact measurements however this caused my application to slow down considerably. The only thing I can figure is that the varchar_pattern_ops operator is significantly slower ??? Is there some other piece of the puzzle to fill in ? Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries, whereas the other one is going to be used for = queries. So you need to keep both indexes. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, Gee, Officer Krupke) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] difference between a unique constraint and a unique index ???
On 12-Nov-07, at 9:56 AM, Alvaro Herrera wrote: Dave Cramer wrote: In order to get like queries to use an index with database initialized with a UTF-8 character set I added a unique index to a table with a varchar_pattern_ops This table already had a unique constraint on the column so I dropped the unique constraint. I can't give exact measurements however this caused my application to slow down considerably. The only thing I can figure is that the varchar_pattern_ops operator is significantly slower ??? Is there some other piece of the puzzle to fill in ? Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries, whereas the other one is going to be used for = queries. So you need to keep both indexes. You would be correct, thanks for the quick answer. Dave ---(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: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 11, 2007, at 2:17 PM, Joshua D. Drake wrote: Dimitri wrote: Seems to me there is more thread model implementation problem on FreeBSD, and databases just reflecting it... Most of the test I done on Solaris show the same performance level on the same short READ- only queries for MySQL and PostgreSQL. And to be honest till the end, thread model should be far faster (context switching between threads is way faster vs processes), but - as I say usually - even a very good idea may be just wasted by a poor implementation... And in case of MySQL they have too much locking to manage concurrency between threads which kills all thread model benefits... Also, to compare apples to apples, they should run this test from remote client rather locally on the same host - however in this case the result for PostgreSQL will mostly depends on client implementation: if client implements reading via CURSOR (quite often), reading will generate 4x times more intensive network traffic than necessary and final PostgreSQL result will be worse... Reading this article I'm just happy for them to see progress done on FreeBSD :-) As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I don't find it strange. I would rather see benchmarks on what the majority of people running on the platform are going to run. Most people don't run 8core machines and they especially don't run 32thread Niagra boxes. Wait! So, what do you check you're email with? :) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Need to run CLUSTER to keep performance
Tom Lane wrote:
Rafael Martinez [EMAIL PROTECTED] writes:
Heikki Linnakangas wrote:
On a small table like that you could run VACUUM every few minutes
without much impact on performance. That should keep the table size in
check.
Ok, we run VACUUM ANALYZE only one time a day, every night.
There's your problem.
Reading between the lines I gather that you think an update is free
in the sense of not creating a need for vacuum. It's not --- it's
exactly equivalent to an insert + a delete, and it leaves behind a
dead row that needs to be vacuumed. If you do a lot of updates, you
need to vacuum.
Hello again
We have more information about this 'problem'.
Tom, we have many other tables which are much bigger and have larger
amount of updates/deletes and are working very well with our actual
vacuum configuration. We are aware of how important is to run vacuum
jobs and we think we have a good understanding of how/why vacuum works.
We think the problem we are seeing sometimes with these small tables is
another thing.
We increased the vacuum analyze jobs, as you all pointed, from one a day
to four every hour (we did not run cluster at all since we started with
this new configuration). We started with this after a fresh 'cluster' of
the table. This has been in production since last week and the
performance of this table only gets worst and worst.
After 4 days with the new maintenance jobs, it took more than 4 sec to
run a select on this table. After running a cluster we are down to
around 50ms. again.
I can not believe 4 vacuum jobs every hour is not enough for this table.
If we see the statistics, it has only ca.67000 updates/day, ca.43
deletes/day and ca.48 inserts/day. This is nothing compare with many of
the systems we are administrating.
What we see in common between these tables (we have seen this a couple
of times before) is:
- Small table size.
- Small amount of tuples in the table (almost constant).
- Large amount of updates compared to inserts/deletes and compared to
the amount of tuples in the table.
You that know the interns of postgres :), can you think of anything that
can be causing this behavior? Any more suggestions? do you need more data?
Thanks in advance :)
We are sending all data we had before the last cluster command and after
it.
--
BEFORE CLUSTER
--
INFO: vacuuming public.hosts
INFO: index hosts_pkey now contains 99933 row versions in 558 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: hosts: found 0 removable, 99933 nonremovable row versions in
3875 pages
DETAIL: 83623 dead row versions cannot be removed yet.
There were 12079 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.03u sec elapsed 0.06 sec.
INFO: vacuuming pg_toast.pg_toast_376272
INFO: index pg_toast_376272_index now contains 133 row versions in 2
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: pg_toast_376272: found 0 removable, 133 nonremovable row
versions in 65 pages
DETAIL: 2 dead row versions cannot be removed yet.
There were 127 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing public.hosts
INFO: hosts: scanned 3875 of 3875 pages, containing 16310 live rows
and 83623 dead rows; 16310 rows in sample, 16310 estimated total rows
scanorama=# SELECT age(now(), pg_postmaster_start_time());
age
-
25 days 22:40:01.241036
(1 row)
scanorama=# SELECT pg_size_pretty(pg_relation_size('hosts'));
pg_size_pretty
30 MB
(1 row)
scanorama=# SELECT count(*) from hosts;
count
---
16311
(1 row)
scanorama=# SELECT
relname,relpages,reltuples,reltoastrelid,reltoastidxid from pg_class
where relname = 'hosts';
relname | relpages | reltuples | reltoastrelid | reltoastidxid
-+--+---+---+---
hosts | 3875 |100386 |376276 | 0
(1 row)
scanorama=# SELECT * from pg_stat_all_tables where schemaname = 'public'
and relname = 'hosts';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
++-+--+--+--+---+---+---+---
105805 | public | hosts | 2412159 | 39109243131 | 3244406 |
9870886 | 1208 | 1685525 | 1088
(1 row)
scanorama=# EXPLAIN ANALYZE SELECT * from hosts;
QUERY PLAN
Seq Scan on hosts (cost=0.00..4878.86 rows=100386 width=314) (actual
time=0.025..4719.082 rows=16311 loops=1)
Total
Re: [PERFORM] Need to run CLUSTER to keep performance
Rafael Martinez wrote: We have more information about this 'problem'. Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of removed pages. We are sending also the output before and after the one we are talking about: ### 2007-11-11_0245.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming public.hosts INFO: index hosts_pkey now contains 110886 row versions in 554 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.00u sec elapsed 0.87 sec. INFO: hosts: found 0 removable, 110886 nonremovable row versions in 3848 pages DETAIL: 94563 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.05s/0.03u sec elapsed 0.94 sec. INFO: vacuuming pg_toast.pg_toast_376272 INFO: index pg_toast_376272_index now contains 260 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_376272: found 0 removable, 260 nonremovable row versions in 65 pages DETAIL: 129 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: analyzing public.hosts INFO: hosts: scanned 3848 of 3848 pages, containing 16323 live rows and 94563 dead rows; 16323 rows in sample, 16323 estimated total rows VACUUM ### 2007-11-11_0301.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming public.hosts INFO: index hosts_pkey now contains 16782 row versions in 556 pages DETAIL: 94551 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.04s/0.09u sec elapsed 590.48 sec. INFO: hosts: removed 94551 row versions in 3835 pages DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: hosts: found 94551 removable, 16695 nonremovable row versions in 3865 pages DETAIL: 372 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.08s/0.16u sec elapsed 590.99 sec. INFO: vacuuming pg_toast.pg_toast_376272 INFO: index pg_toast_376272_index now contains 131 row versions in 2 pages DETAIL: 129 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_376272: removed 129 row versions in 33 pages DETAIL: CPU 0.00s/0.00u sec elapsed 32.05 sec. INFO: pg_toast_376272: found 129 removable, 131 nonremovable row versions in 65 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 51.96 sec. INFO: analyzing public.hosts INFO: hosts: scanned 3875 of 3875 pages, containing 16323 live rows and 576 dead rows; 16323 rows in sample, 16323 estimated total rows VACUUM ### 2007-11-11_0315.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming public.hosts INFO: index hosts_pkey now contains 17363 row versions in 556 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 1.39 sec. INFO: hosts: found 0 removable, 17362 nonremovable row versions in 3875 pages DETAIL: 1039 dead row versions cannot be removed yet. There were 94074 unused item pointers. 0 pages are entirely empty. CPU 0.02s/0.02u sec elapsed 1.43 sec. INFO: vacuuming pg_toast.pg_toast_376272 INFO: index pg_toast_376272_index now contains 131 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_376272: found 0 removable, 131 nonremovable row versions in 65 pages DETAIL: 0 dead row versions cannot be removed yet. There were 129 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: analyzing public.hosts INFO: hosts: scanned 3875 of 3875 pages, containing 16323 live rows and 1040 dead rows; 16323 rows in sample, 16323 estimated total rows VACUUM After this last job the amount of dead rows just continued growing until today. -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 7:
Re: [PERFORM] Need to run CLUSTER to keep performance
Rafael Martinez wrote: DETAIL: 83623 dead row versions cannot be removed yet. Looks like you have a long-running transaction in the background, so VACUUM can't remove all dead tuples. I didn't see that in the vacuum verbose outputs you sent earlier. Is there any backends in Idle in transaction state, if you run ps? In 8.1, CLUSTER will remove those tuples anyway, but it's actually not correct. If the long-running transaction decides to do a select on hosts-table later on, it will see an empty table because of that. That's been fixed in 8.3, but it also means that CLUSTER might no longer help you on 8.3. VACUUM FULL is safe in that sense in 8.1 as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Need to run CLUSTER to keep performance
On Nov 12, 2007 10:11 AM, Rafael Martinez [EMAIL PROTECTED] wrote: Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of removed pages. We are sending also the output before and after the one we are talking about: ### 2007-11-11_0245.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming public.hosts INFO: index hosts_pkey now contains 110886 row versions in 554 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.00u sec elapsed 0.87 sec. INFO: hosts: found 0 removable, 110886 nonremovable row versions in 3848 pages DETAIL: 94563 dead row versions cannot be removed yet. There were 0 unused item pointers. You see that right there? You've got 94k dead rows that cannot be removed. Then, later on, they can: CPU 0.04s/0.09u sec elapsed 590.48 sec. INFO: hosts: removed 94551 row versions in 3835 pages DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: hosts: found 94551 removable, 16695 nonremovable row versions in 3865 pages So, between the first and second vacuum you had a long running transaction that finally ended and let you clean up the dead rows. After this last job the amount of dead rows just continued growing until today. I think you've got a long running transaction that's preventing you from recovering dead rows. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] difference between a unique constraint and a unique index ???
Alvaro Herrera [EMAIL PROTECTED] writes: Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries, whereas the other one is going to be used for = queries. So you need to keep both indexes. Given the current definition of text equality, it'd be possible to drop ~=~ and have the standard = operator holding the place of equality in both the regular and pattern_ops opclasses. Then it'd be possible to support regular equality queries, as well as LIKE, with only the pattern_ops index. This would break any applications explicitly using ~=~, but how many of those are there? (For backwards compatibility it'd be nice if we could allow both = and ~=~ in the opclass, but the unique index on pg_amop seems to preclude that.) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Need to run CLUSTER to keep performance
Scott Marlowe wrote: On Nov 12, 2007 10:11 AM, Rafael Martinez [EMAIL PROTECTED] wrote: Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of removed pages. We are sending also the output before and after the one we are talking about: ### 2007-11-11_0245.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming public.hosts INFO: index hosts_pkey now contains 110886 row versions in 554 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.00u sec elapsed 0.87 sec. INFO: hosts: found 0 removable, 110886 nonremovable row versions in 3848 pages DETAIL: 94563 dead row versions cannot be removed yet. There were 0 unused item pointers. You see that right there? You've got 94k dead rows that cannot be removed. Then, later on, they can: CPU 0.04s/0.09u sec elapsed 590.48 sec. INFO: hosts: removed 94551 row versions in 3835 pages DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: hosts: found 94551 removable, 16695 nonremovable row versions in 3865 pages So, between the first and second vacuum you had a long running transaction that finally ended and let you clean up the dead rows. No, before 8.3, CLUSTER throws away non-removable dead tuples. So the long running transaction might still be there. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Need to run CLUSTER to keep performance
On Nov 12, 2007 11:01 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Scott Marlowe wrote: So, between the first and second vacuum you had a long running transaction that finally ended and let you clean up the dead rows. No, before 8.3, CLUSTER throws away non-removable dead tuples. So the long running transaction might still be there. Wow, good to know. Why would it have changed in 8.3? Was it considered broken behaviour? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Need to run CLUSTER to keep performance
Scott Marlowe wrote: On Nov 12, 2007 11:01 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Scott Marlowe wrote: So, between the first and second vacuum you had a long running transaction that finally ended and let you clean up the dead rows. No, before 8.3, CLUSTER throws away non-removable dead tuples. So the long running transaction might still be there. Wow, good to know. Why would it have changed in 8.3? Was it considered broken behaviour? I certainly considered it broken, though it was a known issue all along. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] difference between a unique constraint and a unique index ???
On 12-Nov-07, at 11:37 AM, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries, whereas the other one is going to be used for = queries. So you need to keep both indexes. Given the current definition of text equality, it'd be possible to drop ~=~ and have the standard = operator holding the place of equality in both the regular and pattern_ops opclasses. Then it'd be possible to support regular equality queries, as well as LIKE, with only the pattern_ops index. That would be ideal. Having two indexes on the same column isn't optimal. Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] ERROR: invalid memory alloc request size or unexpected end of data on large table
(posting on pgsql-perf as I'm questioning the pertinence of the settings, might not be the best place for the overall pb: apologies) Postgresql 8.1.10 Linux Ubuntu: 2.6.17-12-server 4GB RAM, machine is only used for this I do have less than 30 tables, 4 of them having between 10-40 million rows, size on disk is approximately 50G Nothing spectacular on the install, it's mainly sandbox. Relevant bits of the postgresql.conf max_connections = 15 shared_buffers = 49152 work_mem = 16384 maintenance_work_mem = 32768 max_fsm_pages = 4 effective_cache_size = 10 I'm doing a rather 'simplistic' query, though heavy on hashing and aggregate: For the records: select count(*) from action where action_date between '2007-10-01' and '2007-10-31' 9647980 The query is: select tspent, count(*) from ( select sum(time_spent)/60 as tspent from action where action_date between '2007-10-01' and '2007-10-31' group by action_date, user_id ) as a group by tstpent order by tspent asc; I do receive a memory alloc error for a 1.5GB request size. So I may have oversized something significantly that is exploding (work_mem ?) (I was running an explain analyze and had a pgsql_tmp dir reaching 2.9GB until it died with result similar error as with the query alone) ERROR: invalid memory alloc request size 1664639562 SQL state: XX000 Sometimes I do get: ERROR: unexpected end of data SQL state: XX000 table is along the line of (sorry cannot give you the full table): CREATE TABLE action ( id SERIAL, action_date DATE NOT NULL, time_spent INT NOT NULL, user_id TEXT NOT NULL, -- user id is a 38 character string ... ); CREATE INDEX action_action_date_idx ON action USING btree(action_date); Here is an explain analyze for just 1 day: HashAggregate (cost=709112.04..709114.54 rows=200 width=8) (actual time=9900.994..9902.188 rows=631 loops=1) - HashAggregate (cost=706890.66..708001.35 rows=74046 width=49) (actual time=9377.654..9687.964 rows=122644 loops=1) - Bitmap Heap Scan on action (cost=6579.73..701337.25 rows=740455 width=49) (actual time=2409.697..6756.027 rows=893351 loops=1) Recheck Cond: ((action_date = '2007-10-01'::date) AND (action_date = '2007-10-02'::date)) - Bitmap Index Scan on action_action_date_idx (cost=0.00..6579.73 rows=740455 width=0) (actual time=2373.837..2373.837 rows=893351 loops=1) Index Cond: ((action_date = '2007-10-01'::date) AND (action_date = '2007-10-02'::date)) Total runtime: 9933.165 ms -- stephane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] ERROR: invalid memory alloc request size or unexpected end of data on large table
Stephane Bailliez [EMAIL PROTECTED] writes: ERROR: invalid memory alloc request size 1664639562 This sounds like corrupt data --- specifically, 1664639562 showing up where a variable-width field's length word ought to be. It may or may not be relevant that the ASCII equivalent of that bit pattern is Jb8c ... do you work with data that contains such substrings? Sometimes I do get: ERROR: unexpected end of data If it's not 100% repeatable I'd start to wonder about flaky hardware. Have you run memory and disk diagnostics on this machine recently? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
