[PERFORM] select is not using index?
We are suddenly getting slow queries on a particular table. Explain shows a sequential scan. We have "vacuum analyze" ed the table. Any hints? Many TIA! Mark testdb=# \d bigtable Table "public.bigtable" Column | Type | Modifiers -+-+--- id | bigint | not null typeid | integer | not null reposid | integer | not null Indexes: bigtable_id_key unique btree (id) Foreign Key constraints: type FOREIGN KEY (typeid) REFERENCES types(typeid) ON UPDATE NO ACTION ON DELETE NO ACTION, repository FOREIGN KEY (reposid) REFERENCES repositories(reposid) ON UPDATE NO ACTION ON DELETE NO ACTION testdb=# select count(1) from bigtable; count - 3056831 (1 row) testdb=# explain select * from bigtable where id = 123; QUERY PLAN --- Seq Scan on bigtable (cost=0.00..6.00 rows=1 width=16) Filter: (id = 123) (2 rows) testdb=# vacuum verbose analyze bigtable; INFO: --Relation public.bigtable-- INFO: Pages 19200: Changed 0, Empty 0; Tup 3056831: Vac 0, Keep 0, UnUsed 207009. Total CPU 1.03s/0.24u sec elapsed 9.32 sec. INFO: Analyzing public.bigtable VACUUM testdb=# explain select * from bigtable where id = 123; QUERY PLAN --- Seq Scan on bigtable (cost=0.00..57410.39 rows=1 width=16) Filter: (id = 123) (2 rows) -- Mark Harrison Pixar Animation Studios ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] select is not using index?
Corey Edwards wrote: Your column is a bigint but 123 defaults to type int. Indexes aren't used when there's a type mismatch. Use an explicit cast or quote it: select * from bigtable where id = 123::bigint; Or select * from bigtable where id = '123'; Thanks Corey, both of these do exactly what I need... Cheers, Mark -- Mark Harrison Pixar Animation Studios ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Jan Wieck wrote: It might not work with the words I used above, but the point I tried to make is that the hardest thing you can "sell" is a "no". I mean, not just saying "no", but selling it in a way that the customer will not go with the next idiot who claims "we can do that". But you will need some kind of data or reasoning to back up your response, especially if it is deviating from the conventional wisdom, or from some familiar system. Especially in this case, it's not a "no" answer that's being sold... it's "solution a is better than solution b, even though you might be more familiar with solution b." Cheers, Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] coercing int to bigint for indexing purposes
Is there a way to automatically coerce an int into a bigint for indexing purposes? We have a table with a bigint column that is an index. For mi, there's no problem, since I now know to say select * from foo where id = 123::bigint but our casual users still say select * from foo where id = 123 causing a sequential scan because the type of 123 is not a bigint. As you can see, there's nearly 4 orders of magnitude difference in time, and we anticipate this will only get worse as our tables increase in size: LOG: duration:0.861 ms statement: select * from big where id = 123123123123123; LOG: duration: 6376.917 ms statement: select * from big where id = 123; One thing I have considered is starting our id sequence at 50 so that "real" queries will always be bigint-sized, but this seems to me a bit of a hack. Many TIA, Mark -- Mark Harrison Pixar Animation Studios ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] syslog slowing the database?
Tom Lane wrote:
Greg Spiegelberg <[EMAIL PROTECTED]> writes:
If the log and database were on the same disk I'd be okay with the
current workaround. If the ``-'' gave me near the same performance as
turning syslog off I'd be okay with that too. However, neither of these
are the case so there has to be something else blocking between the two
processes.
You could also consider not using syslog at all: let the postmaster
output to its stderr, and pipe that into a log-rotation program.
I believe some people use Apache's log rotator for this with good
results.
I do this... here's the relevant lines from my startup script:
ROTATE="/inst/apache/bin/rotatelogs $PGLOGS/postgresql 86400"
$PGBIN/pg_ctl start -s -D $PGDATA | $ROTATE &
Following is a patch to rotatelogs that does two things:
- makes a symbolic link 'foo.current' that points to the
current output file.
- gzips the rotated logfile
If you have gnu tools installed, you can
tail --retry --follow=name foo.current
and it will automatically track the most recent
log file.
HTH,
Mark
--
Mark Harrison
Pixar Animation Studios
*** rotatelogs.c-orig 2004-03-10 10:24:02.0 -0800
--- rotatelogs.c2004-03-10 11:01:55.0 -0800
***
*** 25,30
--- 25,32
int main (int argc, char **argv)
{
char buf[BUFSIZE], buf2[MAX_PATH], errbuf[ERRMSGSZ];
+ char linkbuf[MAX_PATH];
+ char oldbuf2[MAX_PATH];
time_t tLogEnd = 0, tRotation;
int nLogFD = -1, nLogFDprev = -1, nMessCount = 0, nRead, nWrite;
int utc_offset = 0;
***
*** 75,80
--- 77,84
setmode(0, O_BINARY);
#endif
+ sprintf(linkbuf, "%s.current", szLogRoot);
+ sprintf(oldbuf2, "");
use_strftime = (strstr(szLogRoot, "%") != NULL);
for (;;) {
nRead = read(0, buf, sizeof buf);
***
*** 99,104
--- 103,111
sprintf(buf2, "%s.%010d", szLogRoot, (int) tLogStart);
}
tLogEnd = tLogStart + tRotation;
+ printf("oldbuf2=%s\n",oldbuf2);
+ printf("buf2=%s\n",buf2);
+ printf("linkbuf=%s\n",linkbuf);
nLogFD = open(buf2, O_WRONLY | O_CREAT | O_APPEND, 0666);
if (nLogFD < 0) {
/* Uh-oh. Failed to open the new log file. Try to clear
***
*** 125,130
--- 132,146
}
else {
close(nLogFDprev);
+ /* use: tail --follow=name foo.current */
+ unlink(linkbuf);
+ symlink(buf2,linkbuf);
+ if (strlen(oldbuf2) > 0) {
+ char cmd[MAX_PATH+100];
+ sprintf(cmd, "gzip %s &", oldbuf2);
+ system(cmd);
+ }
+ strcpy(oldbuf2, buf2);
}
nMessCount = 0;
}
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
