Skip site navigation (1) Skip section navigation (2)

query execution time

From: Gena Gurchonok <gena(at)rt(dot)mipt(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: query execution time
Date: 2000-11-30 12:50:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

I've found a strange behavior in query execution:
I have really big table fti_author (>4 000 000 records) and i want to
find ID intersection of ones contain '^clarke' and '^arthur'

I've created 2 methods

1 select all with '^clarke' into temp table
  select intersection of ones with '^arthur' and temp table
(example t1.txt in attachment)
2 one select with set of conditions
(example t2.txt in attachment)

THE PROBLEM is that 1st method requires 0.7sec and the 2nd - 3m20sec.
Please tell me why.


Please find attached to this letter verbose plan for second query.

= selected dump ============================
-- TOC Entry ID 3 (OID 26649711)
-- Name: fti_author Type: TABLE Owner: gena
CREATE TABLE "fti_author" (
        "string" character varying(50),
        "id" oid

-- TOC Entry ID 7 (OID 26649711)
-- Name: "fti_author_s" Type: INDEX Owner: gena

CREATE  INDEX "fti_author_s" on "fti_author" using btree ( "string" "varchar_ops" );

-- TOC Entry ID 8 (OID 26649711)
-- Name: "fti_author_i" Type: INDEX Owner: gena

CREATE  INDEX "fti_author_i" on "fti_author" using btree ( "id" "oid_ops" );


I'm using compiled CVS tree dated
 23 Nov 2000.

complied with options
 --enable-syslog --prefix= --exec-prefix=/usr --with-locale --with-perl

 gcc version pgcc-2.91.66 19990314 (egcs-1.1.2 release)

 Mandrake Linux 6.0 (with some updates)
 Celeron 500/128M

Attachment: t1.txt
Description: text/plain (266 bytes)
Attachment: t2.txt
Description: text/plain (145 bytes)
Attachment: t2_plan.txt
Description: text/plain (5.0 KB)

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2000-11-30 15:56:17
Subject: Re: Both cross-named & compound foreign key constaints fail
Previous:From: Karla PeraltaDate: 2000-11-30 12:21:38
Subject: Support for SQLkit

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group