pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwould 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