http://blog.gmane.org/gmane.comp.db.postgresql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.postgresql.general/163014 <pre>Hi, I'm not into comparing which DBMS is better as we all know ... kind of ... well ... I'd like to find ressources to look up how one can do X in MSSQL when one knows how it is done in PG's SQL and the other way around. regards Andreas </pre> Andreas 2012-05-26T04:04:16 http://comments.gmane.org/gmane.comp.db.postgresql.general/163012 <pre>I'm trying to build postgres 9.2 beta1 on debian stable from sources, in a way that plays nicely with the debian cluster management stuff. If I configure with libdir=/usr/lib/postgres everything works but the shared libraries end up in the wrong place (ie just where I specified). If I configure with libdir=/usr/lib/postgresql/9.2, which is where I want the libraries to go, psql complains: $ /usr/lib/postgresql/9.2/bin/psql: symbol lookup error: /usr/lib/postgresql/9.2/bin/psql: undefined symbol: PQconnectdbParams Here is my configure command: $ ./configure --build=i486-linux-gnu --prefix=/usr --includedir=/usr/include/postgresql/9.2 --mandir=/usr/share/postgresql/9.2/man --infodir=/usr/share/postgresql/9.2/info --sysconfdir=/etc/postgresql-common --localstatedir=/var --libexecdir=/usr/lib/postgresql/9.2 --libdir=/usr/lib/postgresql/9.2 --srcdir=. -docdir=/usr/share/doc/postgresql-doc-9.2 --datadir=/usr/share/postgresql/9.2/ --bindir=/usr/lib/postgresql/9.2/bin --enable-nls --enable-integer-datetimes --enable-thread-safety --enable-debug --disable-rpath --with-perl --with-python --with-pam --with-krb5 --with-gssapi --with-openssl --with-libxml --with-libxslt --with-ldap --with-ossp-uuid --with-gnu-ld --with-system-tzdata=/usr/share/zoneinfo CFLAGS='-g -O2 -g -Wall -O2 -fPIC' LDFLAGS=' -Wl,--as-needed' I'd appreciate any suggestions. In the short term I can live with the libraries being in the wrong place for one postgres version but it isn't viable in the longer term. Ultimately I'd like to be able to build a debian-compatible postgres cluster directly from the repository. Thanks. __ Marc </pre> Marc Munro 2012-05-25T22:05:37 http://comments.gmane.org/gmane.comp.db.postgresql.general/163008 <pre>Hi all I just had the ... pleasure ... of using Windows with Pg again and was in a usability review frame of mind. I landed up trying to restore my database using PgAdmin-III, and was astonished at how difficult and painful it was. The issues weren't all PgAdmin-III either, there are a few Pg-on-Windows issues and a few plain warts in terms of PostgreSQL usability in general. It felt like my first experience with Oracle (ie: screaming, pain and confusion) not the smooth and pleasurable experience I've come to be so used to with Pg. I was sufficiently surprised by some of the issues that I've written up a post on the matter. I intended it to be a few usability notes, though it's turned into a bit more than that. I think it's really imporant to highlight these issues, because if this had been my first experience with PostgreSQL I would have walked away and never, ever, ever come back. It might be premature to post this before I've reviewed and re-edited the post, but hey, a few flames won't hurt. I'm trying to be constructive in the following, just also trying to express the frustration that someone new to Pg would experience. http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html Brief summary of pain points: - The need to back up globals separately even when using a custom-format dump is a giant wart. GIANT. The need to restore them using a completely separate procedure because you can't use pg_restore just makes it even uglier. - Encoding/locale name mismatches between Windows and Linux are really unpleasant, esp when they prevent the restore of backups w/o workarounds! Big, big wart here. - The "restore" dialog in PgAdmin-III needs a lot of love. Harder to access than it should be, some things very counter-intuitive, blocks UI while restore in progress, can't cope with "backups" that're really SQL scripts at all. No interpretation of error codes is offered, which is particularly important because 0-is-success isn't obvious to most people. Messages windows aren't updated until the command completes. The cancel button stays enabled after the command finishes and should be "Abort" not "Cancel" anyway. No interpretation or hints gets presented for common errors. It doesn't exclude mutually exclusive options. Etc etc. Again, see post. - Running a script from PgAdmin-III is way, way too hard. In fact, as far as I can tell, you just can't invoke psql with a script w/o bypassing PgAdmin-III and using the cmdline. Obvious on Linux, but who wants to use a Windows command line? - The PgAdmin-III editor can't stream a file to a Pg backend, it has to load the whole lot into RAM, and it doesn't understand psql syntax even enough to say "whoops, \connect is only supported by psql, did you mean to run this as a script in psql?". It also silently wraps everything in a single transaction, like it or not. - The PgAdmin-III manual contrasts with the excellent quality and coverage of the main PostgreSQL docs. Well, the page on the "Restore" command at least was ... unhelpful. Yes, I'll be submitting improvements. - Pg's habit of continuing after the first error is really hard on newbies. This isn't so much something I ran into during this write-up as something I've been noticing on Stack Overflow. LOTS of people are confused because they see the last error, not the first error that's the cause. It's a lot like gcc - the first error is that you forgot to close a double quote, the rest is garbage you should ignore. A solution to this is needed from a usability standpoint, but is difficult because so much existing code relies on psql in particular continuing after errors. I'd propose having psql cache the first error (ie:non-zero SQLSTATE response) it gets in memory, and emit that when it exists, eg: The first error was: ERROR: role "postgres" already exists ... in response to the command: CREATE ROLE postgres;" Anway, I hope my rant/critique/whine is helpful. -- Craig Ringer </pre> Craig Ringer 2012-05-25T09:56:47 http://comments.gmane.org/gmane.comp.db.postgresql.general/162990 <pre>hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console http://postgres.cz/wiki/Pretty_borders_in_psql Regards Pavel Stehule </pre> Pavel Stehule 2012-05-24T13:33:38 http://comments.gmane.org/gmane.comp.db.postgresql.general/162987 <pre>I have observed by default posgresql 8.x onwards starts as daemon process. Is there a way to avoid it. I want it to run as normal process. The parent of the server process should be the process that starts the server instead of being PID 1 ( init ). Till 7.4.2 ( Which was the older version used by us ) was using running in normal mode. The same behavior is necessary as we have our watchdog failing to identify that it server is actually running. ( Transcript to help you what I am talking about ) c670e04:rkananth 16] /usr/local/bin/postgres --version postgres (PostgreSQL) 9.0.7 c670e04:rkananth 11] postgres -D /data/db/postgres c670e04:rkananth 12] ps -A | grep post 65165 ?? Ss 0:00.01 /usr/local/bin/postgres 65167 ?? Ss 0:00.00 postgres: writer process (postgres) 65168 ?? Ss 0:00.00 postgres: wal writer process (postgres) 65169 ?? Ss 0:00.00 postgres: autovacuum launcher process (postgres) 65170 ?? Ss 0:00.00 postgres: stats collector process (postgres) 65224 0 S+ 0:00.00 grep post c670e04:rkananth 14] ps -p 65165 -o ppid PPID 1 c670e04:rkananth 15] ps 1 PID TT STAT TIME COMMAND 1 ?? SLs 0:00.17 /sbin/init -- I would be happy to provide further information if required. </pre> Karthik 2012-05-24T10:07:28 http://comments.gmane.org/gmane.comp.db.postgresql.general/162981 <pre>If I run this query: select sum(length(html)) from Indexer.Pages; I get: 15,680,005,116 However, if I type: C:\Program Files\PostgreSQL&gt;dir /s I get: Total Files Listed: 5528 File(s) 7,414,385,333 bytes 575 Dir(s) 43,146,137,600 bytes free So all the Postgres data on disk is a little over 7 gigs, however the total sum of bytes in the HTML column of the Pages table is over 15 gigs. Is PG compressing this data? I'm curious as I was considering converting this column to a byte array and gzip'ing the data to save space, however if PG is already doing this for me, then I'm not going to bother. Thanks! Mike </pre> Mike Christensen 2012-05-24T01:07:01 http://comments.gmane.org/gmane.comp.db.postgresql.general/162977 <pre>Hi, I'm running a PG 9.1.3 on OpenSuse 12.1. I found that pg_log is 2 hours ahead though date on the console shows the right date and time. I have the ntp daemon watching the system's time every 60 minutes so this shouldn't be an issue. The time in PG's logfiles filenames as well as the timestamps within the logs are 2 hours ahead. How can I fix this? </pre> Andreas 2012-05-23T23:11:48 http://comments.gmane.org/gmane.comp.db.postgresql.general/162972 <pre>We're getting ready to do system upgrades to our postgresql hosting cluster. Currently, we're using 8-core servers with 32 GB of RAM in each to host approximately 75 end user databases per server. I'm wondering if there are particular performance bottlenecks we should be aware of as we scale the hardware up? Does PG handle 64 GB of RAM well? 128 GB? 16 cores? 48 cores? SAS/SATA III with SSDs? (etc.) A bit of Googling found this thread indicating potential performance issues at 28ish cores a couple years back http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html Thanks, Ben </pre> Lists 2012-05-23T21:37:38 http://comments.gmane.org/gmane.comp.db.postgresql.general/162946 <pre>Hi guys, I'm interested in a solution that will allow our customers to run reports - which may involve complicated queries - on data which is as up-to-date as possible. One thing I don't want to do is to let the reporting system connect to the production database. I want the indexes in production to be limited to what production needs, and not add indexes that are required for reports, for instance. And basically, I don't want a customer to run a complicated report and degrade the performance of my production system. A replication solution is not very good, either, because of course I can't define indexes differently, I don't want *all* transactions in all tables to be sent, and also, because I may want to cross reference data from different systems. So ideally, I want to have a reporting database, where specific tables (or maybe even just specific columns) from various databases are collected, and have a reporting tool connect to this database. But I want to push the data into into that database as close to real time as possible. The most important data I am currently considering are two tables which have an average of 7,600 transactions per hour (standard deviation 10,000, maximum in May is 62,000 transactions per hour). There may be similar pairs of tables collected from more than one database. I assume this is not an uncommon scenario. What solutions would you recommend? Herouth </pre> Herouth Maoz 2012-05-23T13:11:06 http://comments.gmane.org/gmane.comp.db.postgresql.general/162945 <pre>Hi all, in my production environment I have Postgresql 8.2 + PgPool. The port number I specify in PgPool configuration is not a real portnumber (e.g. 123456). I can define a "dblink" as : dblink('dbname=XXXX host=/var/run port=123456 user=XXXX password=XXXX'::text, 'select ... '::text) t1( ... ); or start "psql" as: psql -U XXXX -h /var/run -p 123456 XXXX without problems. In my development environment I upgraded to 9.1 version and the result is: invalid port number: "123456" in both cases. Does Postgresql 9.1 check the "port number" value although I'm connecting through a "Unix domain socket"? Is it a bug or an intended behavior? I noticed that it accepts values up to 65535 . Thanks in advance Alberto </pre> Alberto Zanon 2012-05-23T12:56:26 http://comments.gmane.org/gmane.comp.db.postgresql.general/162943 <pre>I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is "/var/log/postgresql/postgresql-9.1-main.log") contains "???" which indicate some sort of charset encoding problem. But error messages related to pgsql is fine, only other system messages have this problem, for example: 2012-05-19 16:06:12 CST ??: ?????????? 2012-05-19 16:06:10 CST I guess it has something to do with packaging problem rather than postgresql itself, but it would be great if you can give me some clue where the problem might be. My best regards. Yi Huang. </pre> yi huang 2012-05-23T01:15:35 http://comments.gmane.org/gmane.comp.db.postgresql.general/162939 <pre>Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By "blocks" I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks! </pre> Lonni J Friedman 2012-05-22T18:42:35 http://comments.gmane.org/gmane.comp.db.postgresql.general/162936 <pre>Hello I'm implementing HA/failover for my PG nodes. I'm using PG9.0 and async replication and linux. Typical problem - if node 1 fails I want the mirror to become active and take over for the master. The solution should be able to initiate the failover of the standby and start re-directing traffic it. I've spent a lot of time looking at PgPool so I'm well aware that it's perfectly capable of this. However, it feels a little bit like overkill since i don't want it's pooler and I'm not load balancing. The other option would be RHCS, which I know will work, but to implement it with pg replication is a little hacky. So, I'm considering HAProxy, does anyone have experience with that for managing HA, good or bad? Thanks. </pre> David Kerr 2012-05-22T16:36:13 http://comments.gmane.org/gmane.comp.db.postgresql.general/162933 <pre>Why jasperserver has been changed from MySQL to PostGreSQL In previous versions of jasperserver, MySQL was the supporting database but in the jasperserver 4.5 version it changed to PostGreSQL. My question is that why jasperserver changed its mind to PostGreSQL. Which of the features of PostGreSQL are powerful than MySQL? Could you please give me a clue?   Thanks and Regards Farhad Koohbor – BI/DW developer</pre> farhad koohbor 2012-05-22T14:31:24 http://comments.gmane.org/gmane.comp.db.postgresql.general/162930 <pre>stop sending mail </pre> Gugu Nomcebo Mthimkhulu 2012-05-22T14:13:20 http://comments.gmane.org/gmane.comp.db.postgresql.general/162917 <pre>Greetings,   I am trying to get the source XML file for the PostgreSQL installer. This is the BitRock InstallBuilder XML file. Can anyone direct me to the proper place to obtain this installer file?   Thank you, Greg</pre> Greg Simpson 2012-05-22T06:03:49 http://comments.gmane.org/gmane.comp.db.postgresql.general/162916 <pre>Hi, We are using Postgres 9.1.1 on a board with Coldfire controller. The postgres processes are crashing and restarting upon executing a particular instruction and it keeps repeating. Even when we tried with Postgres 9.1.3, same problem happens. It works fine until the FINANCIALTRANSACTIONID reaches 1000. But the same setup is working fine on a windows PC. We have tried to compare the configuration differences between windows PC and the board and found that only difference is the Shared Buffers which is 32 on the PC and 24 on the board. I am pasting the server log from the board here. The line highlighted in yellow is the instruction which is causing the crash. Please let us know why this crash is happening and how we can fix it. LOG: redo starts at 0/D9B75B4 LOG: record with zero length at 0/D9BBE5C LOG: redo done at 0/D9BBE22 LOG: last completed transaction was at log time 2012-05-22 02:22:26.641488+00 LOG: database system is ready to accept connections LOG: autovacuum launcher started ERROR: duplicate key value violates unique constraint "financialtransaction_pkey" DETAIL: Key (financialtransactionid)=(1004) already exists. STATEMENT: Insert into FINANCIALTRANSACTION (ATTENDANT,ENGINEHOUR,RECEIPTPRINTED,FINANCIALTRANSACTIONID) values ('0','0.0','0','1004') LOG: server process (PID 4016) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: poll() failed in statistics collector: Unknown error 516 LOG: statistics collector process (PID 3962) exited with exit code 1 LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-05-22 02:22:29 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 0/D9BBEAA LOG: redo starts at 0/D9BBEAA LOG: record with zero length at 0/D9C07FA LOG: redo done at 0/D9C07C0 LOG: last completed transaction was at log time 2012-05-22 02:23:05.372245+00 LOG: database system is ready to accept connections LOG: autovacuum launcher started ERROR: duplicate key value violates unique constraint "financialtransaction_pkey" DETAIL: Key (financialtransactionid)=(1004) already exists. STATEMENT: Insert into FINANCIALTRANSACTION (ATTENDANT,ENGINEHOUR,RECEIPTPRINTED,FINANCIALTRANSACTIONID) values ('0','0.0','0','1004') LOG: server process (PID 4098) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: poll() failed in statistics collector: Unknown error 516 LOG: statistics collector process (PID 4035) exited with exit code 1 LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-05-22 02:23:08 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 0/D9C0848 LOG: redo starts at 0/D9C0848 LOG: record with zero length at 0/D9C5218 LOG: redo done at 0/D9C51DE LOG: last completed transaction was at log time 2012-05-22 02:23:49.659502+00 LOG: database system is ready to accept connections LOG: autovacuum launcher started Thanks and Regards Jayashankar Larsen &amp; Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. Earth Day. Every Day. </pre> Jayashankar K B 2012-05-22T05:57:20 http://comments.gmane.org/gmane.comp.db.postgresql.general/162909 <pre>Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I made no changes to postgresql.conf following the upgrade. I used pg_upgrade on the master to upgrade it, followed by blowing away $PGDATA on all the standbys and rsyncing them fresh from the master. All of the servers have 128GB RAM, and at least 16 CPU cores. Everything appeared to be working fine until last night when the load on the master suddenly took off, and hovered at around 30.00 ever since. Prior to the load spike, the load was hovering around 2.00 (which is actually lower than it was averaging prior to the upgrade when it was often around 4.00). When I got in this morning, I found an autovacuum process that had been running since just before the load spiked, and the pg_dump cronjob that started shortly after the load spike (and normally completes in about 20 minutes for all the databases) was still running, and hadn't finished the first of the 6 databases. I ended up killing the pg_dump process altogether in the hope that it might unblock whatever was causing the high load. Unfortunately that didn't help, and the load continued to run high. I proceeded to check dmesg, /var/log/messages and the postgresql server log (all on the master), but I didn't spot anything out of the ordinary, definitely nothing that pointed to a potential explanation for all of the high load. I inspected what the autovacuum process was doing, and determined that it was chewing away on the largest table (nearly 98 million rows) in the largest database. It was making very slow progress, at least I believe that was the case, as when I attached strace to the process, the seek addresses were changing in a random fashion. Here are the current autovacuum settings: autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 200000000 autovacuum_max_workers | 4 autovacuum_naptime | 1min autovacuum_vacuum_cost_delay | 20ms autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 Did something significant change in 9.1 that would impact autovacuum behavior? I'm at a complete loss on how to debug this, since I'm using the exact same settings now as prior to the upgrade. thanks </pre> Lonni J Friedman 2012-05-21T21:05:51 http://comments.gmane.org/gmane.comp.db.postgresql.general/162908 <pre>* I am banging my head over this. I want to select distinct values from a varchar column and iterate through the values. * *I want to select the distinct values from this column and loop through them (using as a variable) in a raise notice statement and also in an update statement. I have not been able to do this trying dozens of different approaches. I could not find an example even after searching google. ** So for example, suppose table: mytable has a column "value" that is defined as a varchar: ** for tmp_var in select distinct(value) from mytable where value2='literal' loop raise notice 'I want to print a message here - the tmp_var is [' || tmp_var || ']'; &lt;== error on this line update table set somecolumn = ''' || tmp_var || ''' end loop; I want to use each distinct value in a "raise notice" line and an update statement. ** tmp_var has to be in ' ' ticks or will not work. it is failing on the first FOR statement stating: "invalid input syntax for integer: "some_distinct_value". How do I select varchar distinct values and iterate using variables in a raise notice statement and inside another update statement? ** this seems simple to do , but have not found a way. ** * * * </pre> J.V. 2012-05-21T20:39:40 http://comments.gmane.org/gmane.comp.db.postgresql.general/162903 <pre>I am searching for some logic behind the selection of an index in postgres -- it seems that if I have a composite index based on both columns in a join table, it's only referenced if I query on the first term in the composite index. I've read http://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html over and over and think that this is the same scenario as what I face. As an example: OUTLET: has OUTLET_ID as a primary key, consisting of about a million rows MEDIA: has MEDIA_ID as a primary key, and table consists of only 10 rows OUTLET_MEDIA: a join table used to correlate, and this has about a million rows Each outlet may have 1+ Media (technically, 0 or more, in this schema) Table "public.outlet_media" Column | Type | Modifiers -----------+--------+----------- outlet_id | bigint | not null media_id | bigint | not null Indexes: "outlet_media_pkey" PRIMARY KEY, btree (outlet_id, media_id) Foreign-key constraints: "fkfde1d912281e6fbf" FOREIGN KEY (media_id) REFERENCES media(media_id) "fkfde1d9125014e32a" FOREIGN KEY (outlet_id) REFERENCES outlet(outlet_id) When I test performance, using an OUTLET_ID, the query uses the outlet_media_pkey index # explain analyze select * from outlet_media where outlet_id in (select outlet_id from outlet order by random() limit 50); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=67625.64..68048.50 rows=50 width=16) (actual time=841.115..884.669 rows=50 loops=1) -&gt; HashAggregate (cost=67625.64..67626.14 rows=50 width=8) (actual time=841.048..841.090 rows=50 loops=1) -&gt; Limit (cost=67624.89..67625.01 rows=50 width=8) (actual time=840.980..841.011 rows=50 loops=1) -&gt; Sort (cost=67624.89..70342.66 rows=1087110 width=8) (actual time=840.978..840.991 rows=50 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 27kB -&gt; Seq Scan on outlet (cost=0.00..31511.88 rows=1087110 width=8) (actual time=6.693..497.383 rows=1084628 loops=1) -&gt; Index Scan using outlet_media_pkey on outlet_media (cost=0.00..8.43 rows=1 width=16) (actual time=0.869..0.870 rows=1 loops=50) Index Cond: (outlet_id = outlet.outlet_id) Total runtime: 884.759 ms (10 rows) However if I try the reverse, to search using the MEDIA_ID # explain analyze select * from outlet_media where media_id in (select media_id from media where media_name='Online News'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1.19..21647.53 rows=362125 width=16) (actual time=0.034..0.034 rows=0 loops=1) Hash Cond: (outlet_media.media_id = media.media_id) -&gt; Seq Scan on outlet_media (cost=0.00..16736.76 rows=1086376 width=16) (actual time=0.012..0.012 rows=1 loops=1) -&gt; Hash (cost=1.18..1.18 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 0kB -&gt; HashAggregate (cost=1.17..1.18 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1) -&gt; Seq Scan on media (cost=0.00..1.16 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1) Filter: ((media_name)::text = 'Online News'::text) Total runtime: 0.084 ms (9 rows) Thanks in advance for whatever light can be shed. If it's safer for me to just create individual indexes on each of the two columns (" Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time") regards Bill </pre> Bill Mitchell 2012-05-21T19:34:42 http://comments.gmane.org/gmane.comp.db.postgresql.general/162896 <pre>Hi all, I don't fully understand how is the cost of a bitmap heap scan computed. For instance when the explain output node is similar to the following: Bitmap Heap Scan on test (cost=17376.49..48595.93 rows=566707 width=6) Recheck Cond: ((text1 = 'A'::text) OR (text1 = 'C'::text)) Filter: (num1 &gt; 1) how is the cost of the node (48595.93 - 17376.49) computed? I think it should be something like: (reltuples * ( index_filtering_factor_A + index_filtering_factor_B) ) * (cpu_tuple_cost + cpu_operator_cost) + (reltuples * ( index_filtering_factor_A + index_filtering_factor_B) ) / tuples_per_pages but this does not equal the optimizer cost, so I guess I'm doing something wrong. Suggestions? Thanks, Luca </pre> Luca Ferrari 2012-05-21T13:35:55 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.general