Thread: sequential scans on few columns tables
Hi Recently I was wondering about tables difficult to index. Example - queries with "ilike" where clauses. Without additional contrib modules the only way to search such tables is sequential scan (am I right?) The point is too keep these tables as small as possible. We can do this by denormalizing tables. Let's say we have table "users" which we split into 1:1 relation "users_header" and "users_data". We put searchable columns into users_header and rest of them into users_data. users_data have some integer foreign key referencing to users_header. What do you think about it? Does the Postgres use advantages of small table users_header? Sequential scan on memory cached table should speed up queries, the rest columns are in integer-indexed table which shouldn't slow it down. These example above is ony an idea, I don't have currently any example for it. Regards, Tomasz Myrta
On Mon, Jun 16, 2003 at 21:49:40 +0200, Tomasz Myrta <jasiek@klaster.net> wrote: > Hi > Recently I was wondering about tables difficult to index. Example - > queries with "ilike" where clauses. Without additional contrib modules > the only way to search such tables is sequential scan (am I right?) You might be able to use a functional index depending on exactly what your search patterns are like.
Dnia 2003-06-16 21:55, Użytkownik Bruno Wolff III napisał: > On Mon, Jun 16, 2003 at 21:49:40 +0200, > Tomasz Myrta <jasiek@klaster.net> wrote: > >>Hi >>Recently I was wondering about tables difficult to index. Example - >>queries with "ilike" where clauses. Without additional contrib modules >>the only way to search such tables is sequential scan (am I right?) > > > You might be able to use a functional index depending on exactly what your > search patterns are like. Probably functional indexes won't be helpful to find _substrings_. Tomasz
On Mon, Jun 16, 2003 at 09:49:40PM +0200, Tomasz Myrta wrote: > by denormalizing tables. Let's say we have table "users" which we split > into 1:1 relation "users_header" and "users_data". We put searchable > columns into users_header and rest of them into users_data. users_data > have some integer foreign key referencing to users_header. > > What do you think about it? Does the Postgres use advantages of small > table users_header? Sequential scan on memory cached table should speed > up queries, the rest columns are in integer-indexed table which > shouldn't slow it down. Keep in mind that pgsql has a pretty heafty per-row overhead of 23 bytes. If your data table has a bunch of big varchars then it might be worth it, otherwise it might not be. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"