pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
would number of fields in a table affect search-query time?
4+ messages / 4 participants
[nested] [flat]

* would number of fields in a table affect search-query time?
@ 2004-10-04 23:27 Miles Keaton <mileskeaton@gmail.com>
  2004-10-04 23:32 ` Re: would number of fields in a table affect search-query time? Tom Lane <tgl@sss.pgh.pa.us>
  2004-10-04 23:34 ` Re: would number of fields in a table affect search-query time? Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-10-05 02:06 ` Re: would number of fields in a table affect search-query time? Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Miles Keaton @ 2004-10-04 23:27 UTC (permalink / raw)
  To: pgsql-performance

would the number of fields in a table significantly affect the
search-query time?

(meaning: less fields = much quicker response?)

I have this database table of items with LOTS of properties per-item,
that takes a LONG time to search.

So as I was benchmarking it against SQLite, MySQL and some others, I
exported just a few fields for testing, into all three databases.

What surprised me the most is that the subset, even in the original
database, gave search results MUCH faster than the full table!

I know I'm being vague, but does anyone know if this is just common
knowledge ("duh! of course!") or if I should be looking at is as a
problem to fix?



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: would number of fields in a table affect search-query time?
  2004-10-04 23:27 would number of fields in a table affect search-query time? Miles Keaton <mileskeaton@gmail.com>
@ 2004-10-04 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2004-10-04 23:32 UTC (permalink / raw)
  To: Miles Keaton <mileskeaton@gmail.com>; +Cc: pgsql-performance

Miles Keaton <mileskeaton@gmail.com> writes:
> What surprised me the most is that the subset, even in the original
> database, gave search results MUCH faster than the full table!

The subset table's going to be physically much smaller, so it could just
be that this reflects smaller I/O load.  Hard to tell without a lot more
detail about what case you were testing.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: would number of fields in a table affect search-query time?
  2004-10-04 23:27 would number of fields in a table affect search-query time? Miles Keaton <mileskeaton@gmail.com>
@ 2004-10-04 23:34 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Steinar H. Gunderson @ 2004-10-04 23:34 UTC (permalink / raw)
  To: pgsql-performance

On Mon, Oct 04, 2004 at 04:27:51PM -0700, Miles Keaton wrote:
> would the number of fields in a table significantly affect the
> search-query time?

More fields = larger records = fewer records per page = if you read in
everything, you'll need more I/O.

> I have this database table of items with LOTS of properties per-item,
> that takes a LONG time to search.

It's a bit hard to say anything without seeing your actual tables and
queries; I'd guess you either have a lot of matches or you're doing a
sequential scan.

You might want to normalize your tables, but again, it's hard to say anything
without seeing your actual data.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: would number of fields in a table affect search-query time?
  2004-10-04 23:27 would number of fields in a table affect search-query time? Miles Keaton <mileskeaton@gmail.com>
@ 2004-10-05 02:06 ` Josh Berkus <josh@agliodbs.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2004-10-05 02:06 UTC (permalink / raw)
  To: Miles Keaton <mileskeaton@gmail.com>; +Cc: pgsql-performance

Miles,

> would the number of fields in a table significantly affect the
> search-query time?

Yes.

In addition to the issues mentioned previously, there is the issue of 
criteria; an OR query on 8 fields is going to take longer to filter than an 
OR query on 2 fields.

Anyway, I think maybe you should tell us more about your database design.   
Often the fastest solution involves a more sophisticated approach toward 
querying your tables.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-10-05 02:06 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-04 23:27 would number of fields in a table affect search-query time? Miles Keaton <mileskeaton@gmail.com>
2004-10-04 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-04 23:34 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-10-05 02:06 ` Josh Berkus <josh@agliodbs.com>

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