pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Russell Smith <mr-russ@pws.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Storing binary data.
Date: Thu, 12 Aug 2004 09:44:56 +1000
Message-ID: <200408120944.56155.mr-russ@pws.com.au> (raw)
In-Reply-To: <cfdhhd$ft2$1@sea.gmane.org>
References: <cfdagq$lol$1@sea.gmane.org>
	<200408112042.50356.shridhar@frodo.hserus.net>
	<cfdhhd$ft2$1@sea.gmane.org>

On Thu, 12 Aug 2004 02:29 am, Jesper Krogh wrote:
> I gmane.comp.db.postgresql.performance, skrev Shridhar Daithankar:
> >  On Wednesday 11 Aug 2004 7:59 pm, Jesper Krogh wrote:
> > > The "common" solution, I guess would be to store them in the filesystem
> > > instead, but I like to have them just in the database it is nice clean
> > > database and application design and if I can get PostgreSQL to "not
> > > cache" them then it should be excactly as fast i assume.
> > 
> >  You can normalize them so that a table contains an id and a bytea column only. 
> >  Te main table will contain all the other attributes and a mapping id. That 
> >  way you will have only the main table cached.
> > 
> >  You don't have to go to filesystem for this, I hope.
> 
> Further benchmarking. 
> 
> I tried to create a table with the excact same attributes but without
> the binary attribute. It didn't change anything, so my idea that it
> should be the binary-stuff that sloved it down was wrong. 
> 
> I have a timestamp column in the table that I sort on. Data is ordered
> over the last 4 years and I select based on a timerange, I cannot make
> the query-planner use the index on the timestamp by itself but if I "set
> enable_seqscan = false" the query time drops by 1/3 (from 1.200 msec to
> about 400 msec). 
> 
> I cannot figure out why the query-planner chooses wrong. 
> NB: It's postgresql 7.4.3
> 
Please post explain analyze of the query.

I would guess you are using now() is your query, which is not optimized perfectly
by the planner, so you end up with problems.  But if you post explain analyze 
people will be able to tell you what the problem is.

Maybe on with seqscan on, and one with it off.

Regards

Russell Smith



view thread (5+ messages)

Message-ID: <200408120944.56155.mr-russ@pws.com.au>
Permalink:  ../200408120944.56155.mr-russ@pws.com.au/
Also on:    postgresql.org/message-id/200408120944.56155.mr-russ@pws.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mr-russ@pws.com.au
  Subject: Re: Storing binary data.
  In-Reply-To: <200408120944.56155.mr-russ@pws.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox