[PERFORM] would number of fields in a table affect search-query time?
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? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] would number of fields in a table affect search-query time?
Miles Keaton [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] would number of fields in a table affect search-query time?
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/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] would number of fields in a table affect search-query time?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
