[SQL] sort / limit / range problem
Hi folks,
I've got a glossary table that I'm trying to render to HTML. However, I've
got a problem when using order by and limit. Below is an example of a psql
session showing my problem. Anyone got any reasons why the last select misses
'Driver'?
nymr=# select glterm from glossary where glterm like 'D%' order by glterm;
glterm
Dampers
Dart
Detonators
Disposal
Dome
Draw Bar
Driver
Driving Wheels
Duty Fitter
(9 rows)
nymr=# select glterm from glossary where glterm > 'Driving Wheels' limit 1;
glterm
-
Duty Fitter
(1 row)
nymr=# select glterm from glossary where glterm > 'Draw Bar' limit 1;
glterm
Driving Wheels
(1 row)
nymr=# \d glossary
Table "glossary"
Attribute | Type | Modifier
---+---+-
glid | integer | not null default
nextval('glossary_glid_seq'::text)
glterm| character varying(30) | not null
gldesc| text |
Indices: glossary_pkey,
glossary_term_index
nymr=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] sort / limit / range problem
> Hi folks, > > I've got a glossary table that I'm trying to render to HTML. > However, I've got a problem when using order by and limit. Below > is an example of a psql session showing my problem. Anyone got any > reasons why the last select misses 'Driver'? > Everything is ok. Without order by, limit has undeterminated result. It is described in Postgresql documentation. Try this: select glterm from glossary where glterm > 'Driving Wheels' order by glterm limit 1 select glterm from glossary where glterm > 'Draw Bar' order by glterm limit 1 Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] INTERSECT / where id IN (etc..)
> Greetings. > > I was wondering if somone out there could shed some light on a query > performance issie I have. I have a large lookup table that I query > with nested selects to gain the INTERSECT result from. > > the bigger this table gets the slower the performance. > > 1. is creating a temp table and only intersecting from this a good idea? > > 2. does having a where clause in your select before your nested > intersects change the amount of work the query > > i.e. select person_id from person where person_id < 5000 And > person_id INas opposed toselect person_id from person where > person_id IN(etc) > > thoughts? person_id<5000 probably won't help - postgres will use index on exact person_id if possible. Anyway - for large queries try change IN (...) into EXISTS (...), or just into explicit join if possible. IN clause is rather slow for bigger amount of data. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Insert multiple Rows
>
> If you use a 'prepared' insert within a transaction, would that speed
things
> up - maybe by defering index updates?
>
I doubt it.
From: Neil Conway <[EMAIL PROTECTED]>
... I'd suspect that for most INSERT queries, the
parsing/rewriting/planning stages are relatively inexpensive, and the
bulk of the runtime is spent in the executor (and writing WAL records,
etc). So my guess would be that you won't see that much of a performance
improvement from this...
I've noticed a real performance boost using COPY
something like ...
PQexec("COPY xxx FROM stdin");
for (...)
{
sprintf(buf, "%d\t%d\t...\n",
values[0], values[1], ... values[n]);
PQputline(conn, buf);
}
PQputline(conn, "\\.\n");
PQendcopy(conn);
runs like hell.
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Sorting by NULL values
On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote: > On Tue, 4 Mar 2003, Ian Burrell wrote: > > > I am doing a query where I need to sort by a column that may be NULL > > because it is coming from an OUTER JOIN. I noticed a difference between > > PostgreSQL and other databases about where NULLs show up. It seems that > > with Postgres, NULLs are sorted after other values. Other databases > > sort them before. > > > Is there any standard on how sorting NULLs work? Is there a way to If you care, order by their boolean equivelent first: order by field is null desc, field DESC puts nulls first, since true > false > IIRC, they're either considered greater than or less than non-NULL values, > but the decision is up to the implementation. > > > change Postgres's behavior? Is there a way to replace the NULLs with > > empty strings? > > Coalesce should work. > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[SQL] DELETE FROM A BLACK LIST
Hi all, I have a Blacklist table containing about 1000 ID to delete from another table (T1 about 1440294 records) If i use the SQL DELETE FROM T1 WHERE T1.ID IN (SELECT BLACKLIST.ID FROM BLACKLIST) the operation is very slow . There is a faster way to do the same operation?? Thanks Luca ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] DELETE FROM A BLACK LIST
On Wed, 5 Mar 2003 [EMAIL PROTECTED] wrote: > > Hi all, > I have a Blacklist table containing about 1000 ID to delete from another > table (T1 about 1440294 records) > If i use the SQL > > DELETE FROM T1 WHERE T1.ID IN (SELECT BLACKLIST.ID FROM BLACKLIST) > > the operation is very slow . > > There is a faster way to do the same operation?? use EXISTS > > Thanks > Luca > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
