Essence of Nightshade is a medical substance used in small doses as an anti-anxiety formula, but large doses are fatal. A single drop added to a cup of wine will calm frayed nerves, and three drops will put a person into a deep, dreamless sleep. Ten drops, even diluted into a cup of wine, are fatal. When diluted, it will not change the color of what it is diluted into.
Cersei acquires a bottle from Pycelle when readying for the Battle of the Blackwater. Cersei almost uses the poison on Tommen, to prevent him from being captured when the battle seems lost. When she is halted by the arrival of Tywin to announce victory, she drops the bottle to the floor, smashing it.
When a report is brought to the small council about the massacre of the Starks at the Red Wedding, Joffrey becomes so excited that he shockingly insults his grandfather. Willing himself to behave calmly, Tywin coldly says that the king is "tired" and orders him sent to his chambers to rest - and that Pycelle should administer him some Essence of Nightshade to make sure he sleeps. Joffrey shouts in vain that he's not tired, then slinks away to bed.
In the books
In the A Song of Ice and Fire novels there is no specific medicine called "Essence of Nightshade". in the TV series, it appears to stand in for a variety of analgesic or anesthetic medicines which were present in the novels.
A medicine called "Sweetsleep" has slightly similar properties: it is used as an anticonvulsant and anxiolytic at low doses, though it can be dangerous at high dosages.
The Waif explains to Arya that Sweetsleep is the gentlest of poisons: a few grains will slow a pounding heart and stop a hand from shaking, and make a person feel calm and strong; a pinch will grant a night of deep and dreamless sleep; three pinches will produce that sleep that does not end. The taste is very sweet, so it is best used in cakes and pies and honeyed wine.
Maester Colemon, who treats Robert Arryn by bleeding and Milk of the Poppy, is ordered by Littlefinger to give him a pinch of Sweetsleep to calm him and stop his wretched shaking. Colemon does not seem to like the idea, but does as he is told (it is unclear whether he suspects that Littlefinger has sinister plans to dispose of Robert). He explains to Sansa that a pinch of Sweetsleep can prevent Robert's shaking, but it does not leave the flesh (possibly meaning that even small doses may build up over time, resulting in the effects of larger doses), therefore it is dangerous to use it often.
"Nightshade" has also been mentioned as a poison. The real life deadly nightshade plant, also known as belladonna, has similar properties. Unlike "Essence of Nightshade", this "Nightshade" poison has no medical properties. Actually, Atropa belladona, aka nightshade, produces atropine, which is used to increase heart rate in bradycardia.
When Tywin sends Joffrey to bed after he insulted him in the small council, in the books he tells Pycelle to give Joffrey a substance known as "dreamwine", which is apparently just a very strong kind of wine, administered for medical purposes. It is flavored with exotic spices from Qarth.