Обсуждение: Index ANDing & Index ORing

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Does postgres have support for index ANDing and index ORing?</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-Rob</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div><p><pre wrap="wrap">PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or
entitynamed above.  If the reader of the email is not the intended recipient or the employee or agent responsible for
deliveringit to the intended recipient, you are hereby notified that any use, dissemination or copying of this email
transmissionis strictly prohibited by the sender.  If you have received this transmission in error, please delete the
emailand immediately notify the sender via the email return address or mailto:postmaster@argushealth.com.  Thank you. 



</pre>

Hiltibidal, Robert wrote:
> Does postgres have support for index ANDing and index ORing?

Well, 8.1+ will create an on-the-fly bitmap for x=1 AND y=2 type of queries.

What were you thinking for ORing?

--   Richard Huxton  Archonet Ltd

Hiltibidal, Robert wrote:
> Morning
> 
> I currently use DB2 on an AIX 5.2 p5 platform 7gb ram for log analytics
> with heavy OLAP querying.
> 
> Index ANDing in DB2 allows for multiple indexes to be used in joins. I'm
> not sure of the ORing function yet.

That's what 8.1+ support. The bitmap is in-memory though.

> DB2 does not really allow for WAL to be turned off. Consequently the max
> throughput on queries I have been able to achieve is 367 inserts a
> second. This is on shark disk not local. That number was achieved with 3
> log imports running at once.

Hmm - with battery-backed write-cache on the disks I'd expect better 
than that. Let's see - 367 inserts/sec * 60 =~ 22000 / 3 =~ 7000 inserts 
/min (assuming 1 log). You should be able to do better than that with 
decent disks.

> I'm looking for a solution that allows me to turn WAL on or off
> depending on need. 

I'm not sure what the connection is, but PostgreSQL doesn't allow WAL to 
be turned off. You can turn off fsync, but you can only change this when 
you restart postgresql.

--   Richard Huxton  Archonet Ltd

Hiltibidal, Robert wrote:
> Keep in mind that 2/3 of the inserts are actual log entries. 1/3 are for
> supporting information like ip, query string values etc. 
> 
> The table is a "star" table.
> 
> You are correct tho in terms of time.. When I did the same application
> with fpc pascal (http://www.freepascal.org) and MS SQL 2000 I averaged
> over 700 inserts a second on a Compaq DL380 with 2 gb ram and dual 1.3
> ghz processors. 15000 rpm scsi hard drives in a raid 5 config. 
> 
> The different there is MS SQL 2000 allows transaction logging to be
> turned off. I think the transaction logging presents a "speed bump"

If you can batch things into transactions of 2+ inserts (up to say 5000) 
you'll find things go much faster. The limiting factor should be (as you 
say) the speed of committing the transaction log.

Failing that, try having the WAL on its own disks, with battery-backed 
write cache too.

Failing that, you can turn fsync off, but don't complain if the power 
fails and your database gets corrupted.

--   Richard Huxton  Archonet Ltd